VB MSSQL Connection

[RossM]

Hey,

I am trying to connect to a MSSQL database but i keep getting the error "Compile error: User-defined type not defined". My code is below:

Option Explicit
 
 
Private Sub Text1_Change()
 
Dim cnCon As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
 
Set cnCon = New ADODB.Connection
Set rsProbe = New ADODB.Recordset
 
strSQL = "SELECT blah blah blah"
 
With cnCon
    .Provider = "SQLOLEDB"
    .Properties("Data Source") = "your sql server"
    .Properties("User ID") = "your user id"
    .Properties("Password") = "your password"
    .Open
    .DefaultDatabase = "your default database"
End With
 
With rs
     .ActiveConnection = cnCon
     .CursorType = adOpenStatic
     .CursorLocation = adUseServer
     .LockType = adLockOptimistic
     .Source = strSQL
     .Open
End With
 
MsgBox Text1.Text
 
End Sub
 

Anyone have aly ideas??? thanks alot!!!

[devonknows]

That error is usually associated with when you havnt set the Ado component in your Componenets Menu.

Im assuming it highlights the Dim cnCon As ADODB.Connectio n when you go to debug? if so then...

press CTRL+T or go to your Project menu in visual basic and scroll down to components.

ok, scroll down till you find Microsoft ADO Data Control 6.0 (OLEDB) and check the checkbox next to it and ok,

try and run your application now. Hope this info helps ya

Kind Regards
Devon.

Hey,

I am trying to connect to a MSSQL database but i keep getting the error "Compile error: User-defined type not defined". My code is below:

Option Explicit
 
 
Private Sub Text1_Change()
 
Dim cnCon As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
 
Set cnCon = New ADODB.Connection
Set rsProbe = New ADODB.Recordset
 
strSQL = "SELECT blah blah blah"
 
With cnCon
    .Provider = "SQLOLEDB"
    .Properties("Data Source") = "your sql server"
    .Properties("User ID") = "your user id"
    .Properties("Password") = "your password"
    .Open
    .DefaultDatabase = "your default database"
End With
 
With rs
     .ActiveConnection = cnCon
     .CursorType = adOpenStatic
     .CursorLocation = adUseServer
     .LockType = adLockOptimistic
     .Source = strSQL
     .Open
End With
 
MsgBox Text1.Text
 
End Sub
 

Anyone have aly ideas??? thanks alot!!!

[RossM]

rsProbe =


is highlighted when i run it

[devonknows]

Set cnCon = New ADODB.Connectio n
Set rsProbe = New ADODB.Recordset

if its rsprobe or cnCon the solution i provided earlier should solve that, unless you have tried, but try and it and let me know, it cannot define ADODB as you havnt got it set in components in my best guess.

again hopes this helps

Kind Regards
Devon.

rsProbe =
is highlighted when i run it

[RossM]

I tried what you said and it didn't work.

[devonknows]

Ok, works in my version but sod knows, sorry about that, ok go back to componenets and take out the

Microsoft ADO Data Control 6.0

then go to Project Menu again and go to reference instead of components and scroll down till you find

Microsoft ActiveX Data Objects 2.0 Library or
Microsoft AcviteX Data Objexts 2.1 Library

both should do the same job, personally i use the 2.0 at times.

Let me know how that goes.
Kind Regards
Devon.

[RossM]

Got it working not with another script i found buy thanks alot for your time :)

Also could you tell the connection string i will need to use for this??

Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsCust As ADODB.Recordset
    Dim constr As String
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rsCust = New ADODB.Recordset
 
    constr = "Provider=SQLOLEDB.1;Password=PASS;Persist Security Info=True;User ID=USERNAME;Initial Catalog=????;Data Source=DB_NAME"
    conn.Open constr
 
    rsCust.Open "SELECT * FROM customer"
 

Thats what i got...not sure if the connection string is correct

thanks!

[devonknows]

Couldnt Rightly say but it all looks good, not sure what inital catalog is though, like, your componenets and references are stored in your vbp file just put my mind at ease and have a quick look for me if that reference i mentioned above will do it though, or in your new script see if the ActiveX library that i mentioned earlier is checked, but to be honest that looks correct, you know the old saying though, you wnt know till you try it ;) lol

Kind Regards
Devon

Got it working not with another script i found buy thanks alot for your time :)

Also could you tell the connection string i will need to use for this??

Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsCust As ADODB.Recordset
    Dim constr As String
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rsCust = New ADODB.Recordset
 
    constr = "Provider=SQLOLEDB.1;Password=PASS;Persist Security Info=True;User ID=USERNAME;Initial Catalog=????;Data Source=DB_NAME"
    conn.Open constr
 
    rsCust.Open "SELECT * FROM customer"
 

Thats what i got...not sure if the connection string is correct

thanks!