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)

 

[Quehay]

Suggestions (you may have inherited names...)

--get rid of spaces in table names as a general rule
--use aliases to make the code more legible and compact
--debug.print the SQL string once it's put together, copy to clipboard, create new query in Access and paste SQL into it to see if it works there


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[xlbo]

It returns nothing as you have not asked it to. It runs the query using:

Set rstWOAM = cnn1.Execute(sql1)

but after that, there is no code to bring the data back to the spreadsheet or interogate the recordset.....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MitchPitt]

Quehay,

took your advice about getting the SQL ouput. Pasted it into access the the sql worked fine. I just don't understand why nothing will come out of the recordset when it's placed into VBA.

 

[MitchPitt]

xlbo,

sorry, I didn't place that code after the Set rstWOAM = cnn1.Execute(sql1) code line. I have a while loop that places the recordset values in a spreadsheet. But the while loop fails, because it says there's nothing in the recordset.

 

[xlbo]

how are you interrogating the recordset ??

can you post the rest of the code that should execute the command....?

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[PHV]

And what about the CopyFromRecordset method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MitchPitt]

Do While Not rstWOAM.EOF
        Sheet4.Range("c" & counter).Value =  rstWOAM.Fields("workorder_number").Value
        Sheet4.Range("d" & counter).Value = rstWOAM.Fields("column_value").Value
        Sheet4.Range("e" & counter).Value = rstWOAM.Fields("task_number").Value
        Sheet4.Range("f" & counter).Value = rstWOAM.Fields("SERVICE_ORGANIZATION").Value
        Sheet4.Range("g" & counter).Value = rstWOAM.Fields("REASON_CHANGED").Value
        Sheet4.Range("h" & counter).Value = rstWOAM.Fields("SERIAL_NUMBER_REMOVED").Value
        Sheet4.Range("i" & counter).Value = rstWOAM.Fields("INVENTORY_ITEM").Value
        Sheet4.Range("j" & counter).Value = rstWOAM.Fields("POSITION_APPLIED").Value
        Sheet4.Range("k" & counter).Value = rstWOAM.Fields("SERVICE_MATERIAL_ID").Value
        Sheet4.Range("l" & counter).Value = rstWOAM.Fields("CREATION_DATE").Value
        rstWOAM.MoveNext
        counter = counter + 1
    Loop

 

[MitchPitt]

The code never makes it past the while look since the recordset is considered at End of File

 

[xlbo]

Don't recordsets START at EOF? Seem to remember that you need to move back to the beginning before you can iterate...

Having said that, should be easy to test.

Can either use the RecordCount property of the recordset

msgbox rstWOAM.recordcount

or test to see if BOF = EOF

If there IS data, then you will need to use the MOVEFIRST function BEFORE you iterate OR as PHV has already suggested, you can use the COPYFROMRECORDSET function to dump the whole lot in 1 go with no iteration...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MitchPitt]

Is there any limitations on recordsets with Access 97?

 

[xlbo]

Not sure to be honest - there's about a 1 gig limit on table space memory but other than that I'm not sure

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MitchPitt]

OK. The query should only pick up only a handful of rows if any. The test parameters i'm using should create a single row coming back, but it's not. I've done this coding a millions times and i've never had a problem with a query expression where it would work in access and not in VBA

 

[MitchPitt]

The recordcount message box gave me a count of zero

 

[xlbo]

hmmmm - does the code "step through" ok ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Sorry - should qualify that...does it look like it is executing the query ok ie does the code pause for a short while (on the .execute line) when you use F8 to step through the code ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[MitchPitt]

Yes it does for a couple of minutes since it's querying a datawarehouse.

 

[MitchPitt]

what the query has to do is find what are called workorders that are greater than a particular WO (They are Sequential) and only WOs that have the same 4 middle numbers.

 

[PHV]

In ADO the wildchar is %:
Replace this:
like '" & "ABCD-0123-*" & "'"
By this:
Like 'ABCD-0123-%'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MitchPitt]

Tried the '%' and it didn't work I can't understand the issues with the sql.

here is a condensed version of my sql

 sql1 = "SELECT WOAM.workorder_number, WOAM.column_value, WOAM.task_number, MOAM.SERVICE_ORGANIZATION, MOAM.REASON_CHANGED, MOAM.SERIAL_NUMBER_REMOVED, MOAM.INVENTORY_ITEM, MOAM.POSITION_APPLIED, MOAM.SERVICE_MATERIAL_ID, MOAM.CREATION_DATE"

sql1 = sql1 + " FROM [Armatures - WO after megger] WOAM LEFT JOIN [Material Usage - WO after megger] MOAM ON WOAM.workorder_number = MOAM.WORKORDER_NUMBER"

sql1 = sql1 + " Where WOAM.workorder_number>'BNSF-0521-0210' And WOAM.workorder_number Like 'BNSF-0521%';"