Drew Wutka
DWUTKA at Marlow.com
Mon Dec 8 10:30:38 CST 2008
Hmmm, but this isn't using an ODBC DSN, which is typically why people can't/won't use an ODBC connection, because it requires that the ODBC connection is created as either a local or system DSN, and that's a pain when you have multiple clients. But the SQL I uploaded, doesn't require a DSN at all, just uses the basic Windows ODBC SQL Server driver. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike Mattys Sent: Monday, December 08, 2008 10:19 AM To: Access Developers discussion and problem solving Subject: Re: [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 -- 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.