[AccessD] Insert Into 101 For MAX

Cool indeed Stuart.

John W. Colby
www.ColbyConsulting.com


Darren - Active Billing wrote:
> Stuart 
> 
> This is fantastic - never knew this could be done - pure gold and worked first
> go when I renamed the objects - amazing
> Darryl - you have to check this out  
> Thanks again Stuart and thanks Max too
> 
> DD
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Tuesday, 13 October 2009 10:42 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Insert Into 101 For MAX
> 
> 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"
>