[AccessD] A2003: Getting values from SQL SVR to MDB tmp tables

Brilliant!!!!!!!!!!!!!!!!!!!!!!!

Many thanks (again) :-)
 
Darren 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, 26 January 2010 12:34 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003: Getting values from SQL SVR to MDB tmp tables

From: Me
Subject:Insert Into 101 For MAX
(Original Subject: Insert Into 101)
Date: Tue, 13 Oct 2009 21:42:24 +1000

<quote>
You could use ODBC - link the SQL Server table, do the insert and then
delete the link.

This should do it (aircode so may need some tweaking):

Local strODBCConn as string
Local strSQL as string

strODBCConn = "ODBC;Description=Test Connection;DRIVER=SQL Server;" & _
          "SERVER=SQLServername;APP=Microsoft Data Access Components;" & _
          "DATABASE=SQLDatabasename;Trusted_Connection=Yes"

'Create ODBC Link
DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable, _
        "tblX", "tblX", False

'Insert records
strSQL = "Insert into tblLocalTable Select * from tblX"
CurrentDb.Execute strSQL

'Delete ODBC Link
DoCmd.DeleteObject acTable, "tblX"

</quote>

-- 
Stuart



On 25 Jan 2010 at 23:17, Darren - Active Billing wrote:

> Hi Team
> 
> About 6 months ago (maybe longer) someone posted some very nice, simple
> elegant code that would get data from an SQL dB into a local Access dB
> 
> Essentially replacing the 'heaps' of code I had doing the same thing
> 
> (From memory it avoided loops and the local update was performed with a
> single line - perhaps even a DoCmd statement)
> 
> Anyway - I recall that I even played with it and built a test/sample form
to
> do it and was very impressed by this new way
> 
> Well the time has come for me to use it - And do you think I can find the
> emails, or the sample I built? (Sigh)
> So - if anyone has anything like that (I think Drew or Stuart may have had
a
> hand in the short elegant bit)
> 
> There was a small 'back and forward' between the responding parties and
> myself but I recall nothing more
> 
> I have searched the AccessD archives too - Some look promising but not the
> actual email
> 
> Many thanks in advance
> 
> Darren
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com