Mike Mattys
mmattys at rochester.rr.com
Mon Dec 8 10:44:48 CST 2008
You're usually spot on - let's see what Dan says. - 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 11:30 AM Subject: Re: [AccessD] Move Data from SQL Table to Access FE Table > 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. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com