Access query using Excel VBA

[MitchPitt]

I am having a problem with a query I am trying to run in VBA. When I run the query, it will work with Access 97. But when I put the same SQL code into VBA, it will return nothing. I am baffled by this. Here is a piece of the code below. Some of the stuff was censored.

Dim rstWOAM As ADODB.Recordset
Dim cnn1 As ADODB.Connection

Set rstWOAM = New ADODB.Recordset
Set cnn1 = New ADODB.Connection

Dim stDB1 As String
Dim sql1 As Variant
Dim stconn1 As String

stDB1 = "C:\XXXXXXX.mdb"

stconn1 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & stDB1 & ";"

cnn1.Open (stconn1)

sql1 = "SELECT [Armatures - WO after megger].workorder_number, [Armatures - WO after megger].column_value, [Armatures - WO after megger].task_number, [Material Usage - WO after megger].SERVICE_ORGANIZATION, [Material Usage - WO after megger].REASON_CHANGED, [Material Usage - WO after megger].SERIAL_NUMBER_REMOVED, [Material Usage - WO after megger].INVENTORY_ITEM, [Material Usage - WO after megger].POSITION_APPLIED, [Material Usage - WO after megger].SERVICE_MATERIAL_ID, [Material Usage - WO after megger].CREATION_DATE"

sql1 = sql1 & " FROM [Armatures - WO after megger] inner JOIN [Material Usage - WO after megger] ON [Armatures - WO after megger].workorder_number = [Material Usage - WO after megger].WORKORDER_NUMBER"

sql1 = sql1 & " WHERE [Armatures - WO after megger].workorder_number>'ABCD-0123-1111' and [Armatures - WO after megger].workorder_number like '" & "ABCD-0123-*" & "'"

sql1 = sql1 & " GROUP BY [Armatures - WO after megger].workorder_number, [Armatures - WO after megger].column_value, [Armatures - WO after megger].task_number, [Material Usage - WO after megger].SERVICE_ORGANIZATION, [Material Usage - WO after megger].REASON_CHANGED, [Material Usage - WO after megger].SERIAL_NUMBER_REMOVED, [Material Usage - WO after megger].INVENTORY_ITEM, [Material Usage - WO after megger].POSITION_APPLIED, [Material Usage - WO after megger].SERVICE_MATERIAL_ID, [Material Usage - WO after megger].CREATION_DATE"

Set rstWOAM = cnn1.Execute(sql1)

 

[01Patrik]

When using .movenext on your recordset, and the do - loop functions as you are, you are making yourself voulnerable for spelling mistakes etc.

Take a look at faq707-5844 , which lets you return the Recorset to Excel without defining the column headers and which field going where.

I'm not saying that's neccessarily your problem, but it would exclude some possible issues.


Either way, does the code return any error message at all? From the posts above it seems like it doesn't - and if THAT'S the case, then your code is fine and the SQL string's the problem.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

...since you're JOINing on workorder_number, it seems to me that the entire GROUP BY part is unnesseccary. However, I'm no Sql expert, and the problem might be situated elsewhere. Nut as I said previously, if the code (VBA) is executed without problems, then the issue lies with the sql string.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[MitchPitt]

Hey I figured out the problem. I don't understand why, but the material usage is actually another query I join to. It had criteria that used the "*" for wildcards. So since that did fire the query, then the criteria in the main query wouldn't work. Interesting situation. Thanks everyone for your help!