Stuart McLachlan
stuart at lexacorp.com.pg
Mon Jan 25 07:34:25 CST 2010
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