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

Hi Drew,

I would've said the same, except Dan has been saying that he can't use ODBC.
In the case of SQLS 2005/Expr there is this SQL Native Client which is not 
the
same driver that is used for SQLS 2000.

Spaghetti. :)

-
Michael R Mattys
MapPoint and Database Dev
www.mattysconsulting.com
-
----- Original Message ----- 
From: "Drew Wutka" <DWUTKA at marlow.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Monday, December 08, 2008 10:59 AM
Subject: Re: [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.
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com