[AccessD] Move Data from SQL Table to Access FE Table

Dim strSQL As String
strSQL = "INSERT INTO tblRequests " & _
"SELECT * " & _
"FROM tblRequests AS T1 IN """" [ODBC; DRIVER=" & _
"SQL Server;SERVER=ServerName;DATABASE=DBName;UID=" & _
"PutUserNameHere;PWD=PutPasswordHere] " & _
"WHERE T1.TicketNumber>=27000;"
CurrentProject.Connection.Execute strSQL
MsgBox "Done"

The above worked just fine when I tested it.  Obviously you'll need to
change tblRequests to the Table you are using, and the Where clause to
the appropriate criteria.  And in the 'IN' part, Replace ServerName,
DBName, PutUserNameHere and PutPasswordHere with the appropriate Server,
Database, User and Password.

Drew


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, December 07, 2008 5:58 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Move Data from SQL Table to Access FE Table

I'm learning to use SQL as a BE for Access apps.  The connection method
is
OLEDB rather than ODBC tables.

I would like to copy a list of record ID's from a table in the SQL BE to
a
local FE table each time a process screen is opened.  I'm doing this by
opening a view from Access, creating an ADODB recordset, and then
looping
through that recordset to insert the ID numbers into a local FE table.

But, looping to do this seems quite inefficient for hundreds or
thousands of
rows. 

Is there a better way?

Thanks!
Dan




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.