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

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.





More information about the AccessD mailing list