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

That's what I figured when it came to not using ODBC.  Unfortunately, if
I remember right, I couldn't get the IN clause of the SQL table to use
the OLEDB driver.  Had to be an ODBC driver, but shouldn't be much of a
performance hit, if you can run the 'import' with one SQL Statement,
because you'll only get hit with the initialization of the connection,
the actual work done by the SQL Statement should be roughly the same
speed as with a native driver.

Let us know if that SQL statement works for you.

Drew

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

Hi Drew,

Thanks for your example - I think I'll keep it!  :-)

Some of the users of this app will be in Asia while the server is in
Minneapolis.  We've done some testing and the bandwidth between Asia and
CONUS is 1/10 that of the bandwidth inside CONUS.  In addition, ODBC
table
links have 1/10 the speed that an OLEDB provider does during a
straightforward data transfer comparison test.  Today I'm creating a
test
that will simulate part of the application, so we'll see if they can
actually get reasonable performance.  

I'm using an mdb instead of an Access Project, which makes things a
little
more complicated for an unbound Access app.  I am using a specific
connection provider ("Microsoft.Access.OLEDB.10.0") which allows me to
bind
a form to a connected recordset when needed, but the app will have no
linked
tables.

So, I'm saying that I don't want to use ODBC for bandwidth reasons,
instead
of trying to avoid the complication of setting up a DSN.

What I'm simply going to do is pull over the data, then do a loop to
insert
the data into a local table.  This table only has one field and is only
done
as needed, so hopefully it's not too much of a performance hit.

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Monday, December 08, 2008 9:59 AM
To: Access Developers discussion and problem solving
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.