VB ODBC Connection

[myasin78]

Hello All,
Below is VB code I am using to open an ODBC connection.
I have created a DSN called Patent.

when i run this code i get the following error.
Compile error:
User-defined type not defined

the compile high lite the following line which has the error.

Set dbConn = New ADODB.Connection

can someone help please.

thanks


Private Sub Command1_Click()

Dim buffer As String
On Error GoTo err_openDB
Set dbConn = New ADODB.Connection

dbConn.open "DSN=Patent"

runSql "Set autocommit =0"
Exit Sub

err_openDB
MsgBox "Error opening Database" & Err.Description
Err.Raise Err.Number

Exit Sub
End Sub

 

[myasin78]

here is the updated code i have.
i need to include username and passwd
to open the connection.

the error is:
Error opening Database[Microsoft]]ODBC Driver Manager] Data source name not found and no default driver specified.

thanks,

Private Sub Command1_Click()

On Error GoTo err_openDB
Dim buffer As String
Dim Rs1 As New ADODB.Recordset
Dim dbConn As New ADODB.Connection


buffer = "DSN=Patent"
dbConn.Open "buffer"

Exit Sub

err_openDB:
MsgBox "Error opening Database" & Err.Description
Exit Sub

End Sub

 

[PHV]

Have you tried this ?
"DSN=Patent;"

Hope This Help
PH.

 

[myasin78]

I just started getting

Compile error:
User-defined type not defined

the error line is

dim dbConn = New ADODB.Connection

so I am back to this point.



Private Sub Command1_Click()

Dim buffer As String
Dim dbConn As New ADODB.Connection

buffer = "DSN=Patent;"
dbConn.open buffer

End Sub

thanks again

 

[PHV]

Have you referenced the Microsoft ActiveX Data Objects library in your project ? (Tools --> References ...)

Hope This Help
PH.

 

[myasin78]

i just did,

after I run it i got this error,

Run-time error '-2147467259(80004005)':
[Microsoft][ODBC SQL Server Driver][Named Pipes] Specified SQL server not found


I appreciate your help

 

[PHV]

Your DSN seems not valid.
Try it in Excel with MS-Query before to play with it in VBA.

Hope This Help
PH.

 

[myasin78]

earlier this morning when i created the ODBC, i was able to test it through Access. and then i moved to VB. now i just went back to test again in access but your right i got an SQL Server Login Prompt which reject my user name and passwd.

do you know how to include username and pwd in the VB code
is right like this

buffer = "DSN=Patent&UID=myasin&PWD=Passwd";

thanks

 

[PHV]

Have you tried this ?
buffer = "DSN=Patent;UID=myasin;PWD=Passwd;"

Hope This Help
PH.

 

[myasin78]

I got Access to work again with new ODBC.

the new error in VB is

null password given; logon denied. which is good.

i have tried

buffer = "DSN=Patent;UID=myasin;PWD=Passwd;"
i got this error.
Data source name not found and no default driver specified.

 

[myasin78]

got it !

I missed one semi-colon.

thanks alot.

myasin