asked on

visual basic connect to access database

i am trying to connect an Access database with Visual Basic 6.0 using DAO.

my codes goes like this:

Const CONNECT$ = "ODBC;uid=;pwd=;driver={Microsoft Access Driver (*.mdb)};database=;dsn=myDB;"

Public wrkMaster As Workspace
Public cnnMaster As Connection

Set wrkMaster = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cnnMaster = wrkMaster.OpenConnection("myDB", dbDriverNoPrompt, False, CONNECT$)

whenever i try to run these codes, VB gives me the error 3059 - "Operation cancelled by user"

is there anything wrong with the codes or ODBC setting??

BrianWren

I suspect, though I can't find confirmation of this, that the driver needs to be a quoted string. Additionally, there is really a LOT of apparently excess space in your strings. Again, I'm not certain on this, but I think that what you should have is:

Const CONNECT$ = "ODBC;uid=;pwd=;driver=""Microsoft Access Driver (*.mdb)"";database=;dsn=myDB;"
' A pair of double quotes in a row is coverted into a single double-quote in the
string. "Hello ""Joe"" my friend!" becomes: Hello "Joe" my friend!

Public wrkMaster As Workspace
Public cnnMaster As Connection

Set wrkMaster = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cnnMaster = wrkMaster.OpenConnection("myDB", dbDriverNoPrompt, False, CONNECT$)

Or:

Const CONNECT$ = "ODBC;uid=;pwd=;driver=""{Microsoft Access Driver (*.mdb)}"";database=;dsn=myDB;"

Public wrkMaster As Workspace
Public cnnMaster As Connection

Set wrkMaster = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cnnMaster = wrkMaster.OpenConnection("myDB", dbDriverNoPrompt, False, CONNECT$)

Brian

ASKER CERTIFIED SOLUTION

CEBAKA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial