Open an ODBC connection in a jet workspace and which workspace is better

Open an ODBC connection in a jet workspace and which workspace is better

Post by ryan.d.rem » Thu, 26 Aug 2004 08:07:13


I have TWO questions:

First, I am trying to execute a SQL pass-through query in Access VBA.
According to MSAccess help, I should be able to use the following
syntax after Creating an MSJet Workspace:

wrkJet.OpenDatabase("Publishers", dbDriverCompleteRequired, True, _

where "Pulishers" should be the DSN name per Access help.

I have tried to establish a connection this way and get the message
"file not found (I used the code exaclty as below but instead of an
empty string I included the DSN name.)

I then tried using an empty string instead of a DSN name:

dim SQLWS as Workspace
dim SQLDB as Database
Set SQLWS = CreateWorkspace("SQLWorkspace", CurrentUser, "", dbUseJet)
Set sqlDB = SQLWS.OpenDatabase("", & _
dbDriverNoPrompt, ,

This works, but I get prompted to select the DSN, even though I have
supplied the connection string.

MY FIRST QUESTION IS: How can I get the OPENDATABASE to work without
prompting me to supply the DSN each time if I want to open it up as

My second question is this:
I can open up an ODBC direct connection to the database using an ODBC
direct workspace and a connection object. Are there
drawbacks/disadvantages or advantages to doing so over a JET workspace
and SQLPassThrough?


1. Workspace & Links referenced when workspace opened.

2. Workspace Manager workspace question


I'm trying to find all the versions (workspaces) for a given table.

If I run this query
I get all the workspaces that are different from their parent. (I think I
could add a "where TABLE_NAME = XXX" to get workspaces for a particular
table. Anyone know the exact syntax?)

If I run this query
I get all the workspaces the user has created.

Is there a way to get all the versions a user has created for a table,
whether or not that version differs from its parent?


3. Workspace problems - "not enough workspace available, close some windows and try again"

4. From workspace To workspace problem :/

5. how to clear data from both base Workspace and function workspace

6. GUI workspace and Base workspace interaction

7. From Workspace - To Workspace - Simulink Problem

8. read base workspace from function workspace

9. Why To Workspace variable only exported to Workspace after the simulation is stopped...

10. Workspace setup stalls at http://server/workspace/System/OBE/openwksp.htm

11. provision the workspace as a sub-workspace of another project-by-product?

12. 'mask workspace' in simulink is not a really separate workspace?

13. Copy variables from base workspace to function workspace and viceversa.

14. Simulink problem - To workspace and from workspace

15. Create a Sub-Site, Document Workspace or Meeting Workspace?