I have wriiten a short script in excel vba to connect to a sql server but I would like to use it in a program I'm working on in visual basic 2008 express. The problem is that vb express does not allow the adodb connection I used in excel vba. I need to lnow what to change in the posted code to get it to work. Thanks in advance.
Dim conDB As ADODB.Connection
Dim R As ADODB.Recordset
Dim strConnectionString As String, strSQL As String, strDatabase As String
' Set connection string for database
' This is for a jet database (Microsoft Access)
'strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
'strConnectionString = strConnectionString & "Data Source=" & strDatabase & ";"
' This is for an MSSQL database
strConnectionString = "Data Source=TRAVERSE_WMC;Initial Catalog=wmc;User Id=***;Password=******;"
' Open database
Set conDB = New ADODB.Connection
conDB.ConnectionString = strConnectionString
' Get recordset
strSQL = "SELECT * FROM tblmporder where orderno=31"
Set R = New ADODB.Recordset
R.Open strSQL, conDB, adOpenKeyset, adLockOptimistic
' Now go through records in recordset (if any)
While Not R.EOF
' Example to access field
R.MoveNext ' Move to next record
' Close objects