[AccessD] Insert Into 101 For MAX

You need to create a View in SQL Server based on the query.   You can then link to the 
view just like you do with a table.

-- 
Stuart

On 16 Oct 2009 at 17:31, Darren - Active Billing wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com