[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3155
  • Last Modified:

visual basic 2008 express sql connection

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

0
mwootencnc1
Asked:
mwootencnc1
  • 6
  • 4
  • 2
1 Solution
 
reb73Commented:
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
0
 
grayeCommented:
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)"
0
 
mwootencnc1Author 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?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
reb73Commented:
You can add a reference using the Tools -> References in the VBA editor window (Alt + F11)
test.JPG
0
 
mwootencnc1Author Commented:
I have the reference working in excel vba.  But I don't know how to make it work in visual basic 2008 express.
0
 
grayeCommented:
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"
0
 
mwootencnc1Author 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.
0
 
grayeCommented:
Huh?  .... on the string variable?
0
 
mwootencnc1Author 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?
0
 
grayeCommented:
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
 
0
 
mwootencnc1Author 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.

0
 
mwootencnc1Author Commented:
Thanks for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now