[AccessD] Insert Into 101 For MAX

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"

-- 
Stuart


On 13 Oct 2009 at 21:55, Darren - Active Billing wrote:

> Hi Max
> 
> This looks promising - How would you get it to connect to SQL server on table
X
> and then populate a local Access Table with all the records from table X?
> Many thanks
> Darren
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
> Sent: Tuesday, 13 October 2009 3:40 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Insert Into 101
> 
> As far as I understand the problem, this works ok for me.
> 
> Max
> 
> Sub AppendRecsFromQuery()
>   Dim dbs As DAO.Database, rst As DAO.Recordset
>   Dim sql As String, qdf As DAO.QueryDef
>   Set dbs = CurrentDb
>   sql = "Drop  table _TestA"
>   dbs.Execute (sql)
> 
>   sql = "Create Table _TestA (FirstName text, Surname Text, PersonID long)"
>   dbs.Execute (sql)
> 
>   sql = "Drop Table _TestQ"
>   dbs.Execute sql
> 
>   sql = "Select firstname,surname,PersonID from mcmPeople Where PersonID <
> 1000"
>   Set qdf = dbs.CreateQueryDef("_TestQ", sql)
>   Set rst = dbs.OpenRecordset(qdf.Name, dbOpenSnapshot)
>   sql = "insert into _TestA " & qdf.sql
>   dbs.Execute sql
> 
> End Sub
> 
> 
> 
> -- 
> 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


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