Access 2003 ADP and SQL Server 2000

[dougloj]

Hi.


I'm trying to access an SQL Server 2000 database from an Access 2003
project. I want to call stored procedures to get and update data in the
database. If I create a connection for the entire Access project, I am
able to call the stored procedures from my VBA code to do this with no
problem, but I have some questions:


1) Does the project connection to the SQL Server database stay open all
the time? It seems that it doesn't matter whether or not I try to open
or close the connection explicitly in my VBA code. It still works
either way. Does Access some how open and close the connection behind
the scenes when needed?


2) If the entire Access project is connectioned to the SQL Server
database, is there any way to create "local tables" that are only seen
from within the Access project and not created in the server database?
If I try to create a table from within the connected project, the table
appears in the server database. Is there a way to avoid this?


3) If I don't create a connection for the entire Access project, but
instead try to open a connection from within my VBA code, I get an
error which states that my OLE DB provider does not currently have a
session object. Does anyone have any ideas about this?

Thanks for the bandwidth!

Doug

 

[Alex]

Hi Doug,

I personally use the currentproject.connection within the access
database, this works against SQL server if your project is an ADP which
you have stated above.

e.g.

dim adoTest as new adodb.recordset

adoTest.Open "Select myfield from myTable", currentproject.connection,
cursor type, lock type

using this method means you don't have to setup the connection
yourself.

As far a I know the connection is on all the time the ADP is running.

the local tables question, because it is an ADP you don't have local
tables, they are all SQL server tables, what are these tables for.

lookup lists, temp tables or something else.

Hope I have answered you questions.

Regards

Alex