ODBC Connection

[jwbatey07]

I've never attempted to connect to our SQL server using VBA. I do it all the time in Excel through the 'Data' tab. My goal is to write a VBA code that will query our database to find the status of a customer's account. ideally, I would use a comboxbox to lookup the customer by customer name or employee name, the data coming back would be their credit status. if the status is good it would allow our technician to make their product, if the status is "hold", it would not allow the form to come up. that's the goal. I found this code below that I think I can make work. I've removed the credentials for security purpose but I have check the connection by itself and it does connect. I have yet to write in the JOINS that I will be needing. At this point, I just want to be able to bring back the information from my database and put it into a worksheet. once I have the I can attempt to figure out the rest. when I run this code I get a syntax error on line

rs.Open mssql, oConn


Hopefully someone can help me out with this.


Sub DatatakenFromSQLServer()

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim mssql As String
Dim row As Integer
Dim Col As Integer
Dim ws As ThisWorkbook
Set ws = ThisWorkbook
Application.ScreenUpdating = False
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
mssql = "SELECT p21_view_contacts.id, p21_view_contacts.first_name, p21_view_contacts.last_name" & _
"FROM dbo.p21_view_contacts"
oConn.ConnectionString = "Driver={SQL Server}; Server=; Database=; VID=;PWD=" INFORMATION REMOVED FOR SECURITY PURPOSE

oConn.ConnectionTimeout = 30
oConn.Open
rs.Open mssql, oConn ERROR "INCORRECT SYNTAX NEAR"
If rs.EOF Then
MsgBox "No matching records found."
rs.Close
oConn.Close
Exit Sub
End If

row = 5
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld.Name
Col = Col + 1
Next
rs.MoveFirst
row = row + 1
Do While Not rs.EOF
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld
Col = Col + 1
Next
row = row + 1
rs.MoveNext
Loop
rs.Close
oConn.Close

End Sub

 

[jwbatey07]

perhaps it's my SQL Statement. i've tried many variations, with the table name without the table name, with quotes. Here is an example of a statement I wrote for Bartender.


SELECT "p21_view_inv_tran"."document_no", "p21_view_inv_tran"."sub_document_no", "p21_view_inv_tran"."qty_allocated", "p21_view_inv_tran"."item_id", "p21_view_inv_tran"."trans_date", "p21_view_oe_hdr"."ship2_name", "p21_view_oe_hdr"."taker", "p21_view_oe_hdr"."po_no", "p21_view_address"."name", "p21_view_address"."id"
FROM "dbo"."p21_view_inv_tran"
INNER JOIN "dbo"."p21_view_oe_hdr" ON "dbo"."p21_view_inv_tran"."document_no" = "dbo"."p21_view_oe_hdr"."order_no" INNER JOIN "dbo"."p21_view_address" ON "dbo"."p21_view_oe_hdr"."carrier_id" = "dbo"."p21_view_address"."id"
WHERE sub_document_no
BETWEEN ?sub_document_no AND ?sub_document_no1

 

[jwbatey07]

i figured it out. no need for help