[AccessD] Insert Into 101 For MAX

Hi Stuart

I have started to use this method for table to table 'links/import

How would I go about doing this where I would like to use a query on the SQL
Server Side instead of a table on the SQL Server side

EG this is what I'd like to do - but of course an SQL statement is not an object
that the transferSpreadsheet routine can find

Dim selSQL as string
selSQL = "Select *, aa.SomeOtherFieldFromSomeOtherTable as SomeField from
Account  join SomeOtherTable aa on a.AccountNo = aa.AccountNo"
           
DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable, selSQL,
"tblAccount", False

Thanks


-----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