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??
oddzAsked:
Who is Participating?
 
CEBAKAConnect With a Mentor Commented:
Connect$="dsn=myDB;" should be enough. But you have to make a odbc entry manual. Could you check this?
0
 
BrianWrenCommented:
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
0
All Courses

From novice to tech pro — start learning today.