Hi Expert,

I already create a datasource name for my Access database. However, I failed to connect to that database using this code:

Set db = DBEngine.Workspaces(0).OpenDatabase("dbsourcename", ,"DSN=dbsourcename;UID=MyUID;PWD=Mypassw")

Could anyone help me please. Thank.

What version of VB are you using? And, what is DBEngine declared as?

dont know much about VB @ all but let me ask u a coupla ???...

what db are u trying to access the Access DB from?

does it support ODBC via VB directly, or do you need connection services, managers &/or drivers?

is it necessary to establish a connection authorization from your destination db to your external source (ODBC) db PRIOR to attempting connection?

i come from the Lotus Notes/Domino world, whereby the answers to all of the above is a resounding YES, but i do not know if that is universally true.

Just wanna check up on something ...

Personally, I've never connected to a database via ODBC. However, just wanna check:

You say you used the command
Set db = DBEngine.Workspaces(0).OpenDatabase("dbsourcename", ,"DSN=dbsourcename;UID=MyUID;PWD=Mypassw")
to connect to the database. You *DID* replace the items in bold with your own variables, right? Just making sure!

Also ... one more thing ... between dbsourcename and DSN=, you have two commas. Is it supposed to be like that. You use a ; to separate all of the other parameters.

Just a lil syntax check you may wanna look at!

Dani -
Thats wrong :-P. When you use a DSN, you do not need to fill in any other information. The DSN stores everything for you.

Also you seperate connnectionstring items with a ;

What I'm saying is this is the code that ChristinaOrchid used. It doesn't matter whether they're using extraneous info, etc. It might be a syntax error (e.g. they used a comma instead of a semi-colon.)

You just said you don't need to fill in any other info? Well they did (as you can see). Maybe that's the problem?

Just trying to troubleshoot the code THEY used from a syntax error standpoint.

If your using VB.NET or C#.NET the following link is your end-all resource!

http://www.connectionstrings.com/

The following is if you're using VB.NET:

Access
ODBC

Standard Security:


Workgroup:

OLEDB, OleDbConnection (.NET)


Standard security:


Workgroup (system database):


With password:

DSN

DSN:


File DSN:

Hi All,

I will try to describe more clearly my problem:
I am working on my Excel workbook and I create a button trying to connect to Access database or Oracle database that I already created datasource name for those 2 database in ODBC window. However, when I write the code like this:
Set db = DBEngine.Workspaces(0).OpenDatabase("", , , "ODBC;")
this open an ODBC window and let me select a datasource name. When I select an Oracle datasource name, the connection is successful. However, when I select an Access datasource name, I got the message error <<You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database. (Error 3423)>>

So, My first question is why I cannot access to Access database??

Second, I trie to test to connect to Oracle/Access database by passing the datasource name as follow:

Set db = DBEngine.Workspaces(0).OpenDatabase("Oracle7", , , "DSN=Oracle7; DBQ=sqn2_mars; UID=MyuserId; PWD=Mypwd"), the effect is the same as if my first example on the top.


Hi Tekmaven,
I did'nt declare my DbEngine. As I know DbEngine is the top level object in the DAO and we can use without declaring it.

Hi aeinstein,
My problem is when I trie to connect to Access database.

Hi cscgal,
I have compied my code and there is no error syntaxe that's why it open the ODBC window and let me select my datasource name.


Thank All for your response, I am looking for all your new response days and days :p)

ChristinaOrchid.

I forget to tell that I am using Microsoft Excel 97 .

Huh? Are you tring to import data from a table? If you are, just selecet File->Open and change the file type to "Acess Databases." You will recieve a wizard that will automatticly import the data for you!