VB MSSQL Connection

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.Connection 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:

Expand|Select|Wrap|Line Numbers

1. Option Explicit
2.  
3.  
4. Private Sub Text1_Change()
5.  
6. Dim cnCon As ADODB.Connection
7. Dim rs As ADODB.Recordset
8. Dim strSQL As String
9.  
10. Set cnCon = New ADODB.Connection
11. Set rsProbe = New ADODB.Recordset
12.  
13. strSQL = "SELECT blah blah blah"
14.  
15. With cnCon
16.     .Provider = "SQLOLEDB"
17.     .Properties("Data Source") = "your sql server"
18.     .Properties("User ID") = "your user id"
19.     .Properties("Password") = "your password"
20.     .Open
21.     .DefaultDatabase = "your default database"
22. End With
23.  
24. With rs
25.      .ActiveConnection = cnCon
26.      .CursorType = adOpenStatic
27.      .CursorLocation = adUseServer
28.      .LockType = adLockOptimistic
29.      .Source = strSQL
30.      .Open
31. End With
32.  
33. MsgBox Text1.Text
34.  
35. End Sub
36.  

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

rsProbe =


is highlighted when i run it

Set cnCon = New ADODB.Connection
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

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

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.

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??

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

thanks!

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??

Expand|Select|Wrap|Line Numbers

1. Dim conn As ADODB.Connection
2.     Dim rs As ADODB.Recordset
3.     Dim rsCust As ADODB.Recordset
4.     Dim constr As String
5.     Set conn = New ADODB.Connection
6.     Set rs = New ADODB.Recordset
7.     Set rsCust = New ADODB.Recordset
8.  
9.     constr = "Provider=SQLOLEDB.1;Password=PASS;Persist Security Info=True;User ID=USERNAME;Initial Catalog=????;Data Source=DB_NAME"
10.     conn.Open constr
11.  
12.     rsCust.Open "SELECT * FROM customer"
13.  

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

thanks!