[AccessD] Connecting MS Access 2000 to SQL Server 2000

Paul,

Instead of linking why not pull down the information using
DoCmd.TransferDatabase?

I have this code on one of my apps.

DoCmd.DeleteObject acTable, "myaccesstablename"

DoCmd.TransferDatabase acImport "ODBC Database",
"ODBC;DSN=mydsnconnectionname;UID=myuserid;PWD=mypassword;Language=us_englis
h;" _
& "DATABASE=mysqldatabasename", acTable, "mysqltablename",
"myaccesstablename" 

ODBC by its very nature is slow and I've had the same issues before trying
to link SQL tables. This approach has solved that for me.

Eric

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul M. Jones
Sent: Wednesday, July 27, 2005 10:14 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Connecting MS Access 2000 to SQL Server 2000

I have an application initially written in Access 97 and then moved to 2000.
It talks to a SQL Server 2000 database using tables linked through an ODBC
driver.  Lately I have had several instances where I call a stored procedure
to perform an operation but I don't receive a response from the SQL Server
even though the operation was carried out successfully.  This leaves my
application hanging.  I suspect it has to do with the ODBC connection.
Anybody has any experiences with this type of issue?

On a related note, is there a better way of linking my tables to the SQL
Server other than to use an ODBC driver?  I have a couple of newer projects
that use ADP but unfortunately, it's not an option for me at this point
since I have too many queries that I'll need to convert to either stored
procedures or views.

Thanks

Paul M. Jones


----------------------------------------------------------------------------
------------------
Reality is the murder of a beautiful theory by a gang of ugly facts.
         Robert L. Glass 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


                                                                                                                                                                      
                                                                                                                                                                                                             ----------------------------------------------------------------
The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted 
herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be 
disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the 
sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner.  If you 
are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any 
disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY 
PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.
The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of 
email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not 
to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is 
contrary to company policy. The company will not accept any liability in respect of such communication.