VBA from Jet database vs VBA from SQL

[richertt]

Hello everyone,

At work, we have a MRP system using a jet database backend where I pull various information an bring into Excel thru VBA. Recently, the Jet database was migrated over to SQL. Now my VBA is broken. Specifically, the "Where" and "And" statements. If I remove the two line, I pull data from SQL just fine. I'm sure its just a syntax error but I don't have a clue what it should look like. I have many VBA projects with similar code so fixing this should help fix 20 more. Please help!

Const JET As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vm-server\MRP Target\MiniMRP5\data\MRP5Data; Persist Security Info=False;"
Const SQL As String = "Provider=MSOLEDBSQL;Server=vm-server;Database=minimrp_db5;UID=sa;PWD=Angel0A;"

Sub BOM()

Dim Conn As Object
Dim Data As Object
Dim Field As Object

Set Conn = CreateObject("ADODB.Connection")
Set Data = CreateObject("ADODB.Recordset")

Conn.ConnectionString = SQL
Conn.Open

On Error GoTo CloseConnection


With Data
.ActiveConnection = Conn
.Source = getSQLString
.LockType = 1
.CursorType = 0
.Open
End With

On Error GoTo CloseRecordset

Worksheets.Add
For Each Field In Data.Fields
ActiveCell.Value = Field.Name
ActiveCell.Offset(0, 1).Select

Next Field

Range("A1").Select
Range("A2").CopyFromRecordset Data
Range("A1").CurrentRegion.EntireColumn.AutoFit

On Error GoTo 0

CloseRecordset:
Data.Close

CloseConnection:
Conn.Close
'___________________________________________________

Function getSQLString() As String
Dim PCA As String
Dim REV As String
Dim SQLstring As String

PCA = InputBox("Enter PCA #")
REV = InputBox("Enter REV")

SQLstring = "SELECT tblUsedIn.LineItemNo, tblStockItems_1.MasterPNo, tblStockItems_1.ItemDescription, tblUsedIn.QtyPer, tblUsedIn.RefText " & _
"FROM tblStockItems AS tblStockItems_1 INNER JOIN (tblStockItems INNER JOIN tblUsedIn ON tblStockItems.ItemID = tblUsedIn.ParentID) ON tblStockItems_1.ItemID = tblUsedIn.ChildID " & _
"where tblStockItems.MasterPNo = """ & PCA & """" & _
"and tblStockItems.Rev = """ & REV & """" & _
"order by tblUsedIn.LineItemNo"

getSQLString = SQLstring

End Function

 

[jimrward]

What happens if you debug.print or message the sql string to check for spacing and quotation marks to make sure it is being correctly constructed
you can also copy the resultant string into your sql query interface to see if it executes

 

[richertt]

What happens if you debug.print or message the sql string to check for spacing and quotation marks to make sure it is being correctly constructed
you can also copy the resultant string into your sql query interface to see if it executes

The data I enter into my input boxes correctly display in the immediate window when I debug.print. If I replace """ & PCA & """" and """ & REV & """" with the actual values, the code works fine.

 

[richertt]

The data I enter into my input boxes correctly display in the immediate window when I debug.print. If I replace """ & PCA & """" and """ & REV & """" with the actual values, the code works fine.

Something else just occurred to me. In a jet database my input is surrounded by " ". in SQL, my input needs to be surrounded by ' '. The problem is, when I use ', VBA thinks its a comment. How do I code an ' in VBA?