We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

visual basic 2008 express sql connection

Medium Priority
3,185 Views
Last Modified: 2013-11-27
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.
Sub main()
 
  
    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
   conDB.Open
    
    ' 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)
    R.MoveFirst
    While Not R.EOF
        ' Example to access field
        MsgBox R.Fields("assemblyid").Value
        R.MoveNext ' Move to next record
    Wend
    
    ' Close objects
    R.Close
    conDB.Close
 
End Sub

Open in new window

Comment
Watch Question

Commented:
Read through the article in this attached link about using data/table adapters for connecting to a database backend..

http://msdn.microsoft.com/en-us/library/tkctt675.aspx

Commented:
Visual Basic 2008 would much rather have you use the newer ADO.Net library, but you *can* continue to use the older "classic" ADO...  you just have to "Add a Reference" to the COM-based 'Microsoft ActiveX Data Objects Library (ADO)"

Author

Commented:
I have seen a few post on this sight about adding the ado reference but never a sample code on how to do so. Do you have any examples?

Commented:
You can add a reference using the Tools -> References in the VBA editor window (Alt + F11)
test.JPG

Author

Commented:
I have the reference working in excel vba.  But I don't know how to make it work in visual basic 2008 express.
Commented:
From the Solution Explorer (typically in the upper right-hand corner of the Visual Studio IDE), right click your application, and select the "Add Reference" menu.    A dialog box will appear that has several "tabs" across the top.   You'll need the 2nd one, called "COM".   From there, search thru the huge list of items until you get to "Microsoft ActiveX Data Objects 2.8 Library"

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
OK I was able to find and ad the reference and I was think the above code would work but now I get errors any where I call a string such as strconnectionstring the error says declaration expected.

Commented:
Huh?  .... on the string variable?

Author

Commented:
yes with the code posted above in visual basic 2008 express it has an error at all of the string variables of declaration expected. Am I missing something simple?

Commented:
I don't see anything that sticks out....  
There is a new optional way to declare multiple items on the same line:
Dim strConnectionString As String, strSQL As String, strDatabase As String
can be shortened to:
Dim strConnectionString. strSQL, strDatabase As String
 

Author

Commented:
Graye I don't know why it was not working I deleted the project and started a new one and added the references and it worked fine.  Thanks for all your help.

Author

Commented:
Thanks for your help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.