Recordset writing ÿþ to CSV file instead of query results

Question

I have a VBS file that queries an access database and writes the results to a .CSV file. The only thing being written to the .CSV file is ÿþ.

I am using the same VBS file with a more complex query and it runs fine. Both queries return 2 fields of the same type. The only change between the 2 files is the SQL query. Also if I paste the query I'm trying to use into Access, it runs the query as expected.

Here is the VBS file:

Dim connStr, objConn, getNames, objFSO, rs
Const ForWriting = 2

'Make and open progress window
On Error Resume Next

Set objExplorer = CreateObject("InternetExplorer.Application")
objExplorer.Navigate "about:blank"
objExplorer.ToolBar = 0
objExplorer.StatusBar = 0
objExplorer.Width = 400
objExplorer.Height = 200
objExplorer.Visible = 1
objExplorer.Silent = 1
objExplorer.Document.Title = "Script in progress"
objExplorer.Document.Body.InnerHTML = "Your Script is being processed. " & "This might take several minutes to complete.  Closing this window will stop the script. So don't close this window ^_^"

'Create csv file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("\\192.168.100.4\data\IT\Scripts\testData.csv", ForWriting, True)

'Define Db String
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\192.168.100.16\Sage\2016\Connex\Sage100Shadow_CHRIS.mdb"

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.Open connStr

'Define recordset and SQL query
Set rs = objConn.Execute("SELECT [BillNo],[InStock] FROM qryKitInvNums;")

MsgBox(rs.Fields(0).Type & ", " & rs.Fields(1).Type)

'While loop, loops through all available results
Do While Not rs.EOF
    'add values seperated by comma to getNames
    getNames = rs.Fields(0) & "," & rs.Fields(1)
    'Write current record to LogFile
    objLogFile.Write getNames
    'Line return for next record in LogFile
    objLogFile.Writeline

    'move to next result before looping again
    rs.MoveNext
    'continue loop
Loop

MsgBox(getNames)

'Close connection and release objects
objLogFile.Close
objConn.Close
Set rs = Nothing
Set objConn = Nothing

'Closes progress window
objExplorer.Document.Body.InnerHTML = "Your Script is now complete."

WScript.Sleep 2000

objExplorer.Quit

Here is a copy of the working VBS file. The only difference is the SQL query which still returns 2 fields of same type as the first VBS file.

Dim connStr, objConn, getNames, objFSO
Const ForWriting = 2

'Make and open progress window
On Error Resume Next

Set objExplorer = CreateObject("InternetExplorer.Application")
objExplorer.Navigate "about:blank"
objExplorer.ToolBar = 0
objExplorer.StatusBar = 0
objExplorer.Width = 400
objExplorer.Height = 200
objExplorer.Visible = 1
objExplorer.Silent = 1
objExplorer.Document.Title = "Script in progress"
objExplorer.Document.Body.InnerHTML = "Your Script is being processed. " & "This might take several minutes to complete.  Closing this window will stop the script. So don't close this window ^_^"

'Create csv file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("\\192.168.100.4\data\IT\Scripts\testData.csv", ForWriting, True)

'Define Db String
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\192.168.100.16\Sage\2016\Connex\Sage100Shadow_CHRIS.mdb"

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.Open connStr

'Define recordset and SQL query
Set rs = objConn.Execute("SELECT IM_ItemWarehouse.ItemCode, IIf([InStock] Is Null Or [InStock]="""",[IM_ItemWarehouse].[QuantityOnHand],[InStock]) AS EvalInstock FROM IM_ItemWarehouse LEFT JOIN qryInStockNum4ItemsWPO ON IM_ItemWarehouse.ItemCode = qryInStockNum4ItemsWPO.ItemCode WHERE (((IM_ItemWarehouse.ItemCode) Is Not Null) AND ((IM_ItemWarehouse.WarehouseCode)=""000""));")

'While loop, loops through all available results
Do While Not rs.EOF
    'add values seperated by comma to getNames
    getNames = rs.Fields(0) & "," & rs.Fields(1)
    'Write current record to LogFile
    objLogFile.Write getNames
    'Line return for next record in LogFile
    objLogFile.Writeline

    'move to next result before looping again
    rs.MoveNext
    'continue loop
Loop

'Close connection and release objects
objLogFile.Close
objConn.Close
Set rs = Nothing
Set objConn = Nothing

'Closes progress window
objExplorer.Document.Body.InnerHTML = "Your Script is now complete."

WScript.Sleep 2000

objExplorer.Quit

Your query returns an empty result. The only thing that gets written into the file is the [Unicode BOM](https://stackoverflow.com/a/26493475/11683). — GSerg, Aug 21 '17 at 17:16
The query gives me results in Access though. And my other VBS file pulls the result of the query just fine. Is there a way to check the recordset? I'm not sure how to troubleshoot here since the query works in the Access DB that my VBS file is accessing. — Chris P, Aug 21 '17 at 17:20
Then there must be an error after creating the csv file. Remove the `on error resume next` and find out. — GSerg, Aug 21 '17 at 17:22
Generelly, except in very very special situations, you should keep `on error resume next` off. You never ever want to set that globally. — Tomalak, Aug 21 '17 at 17:25
Thank you for the advice. I removed the 'on error resume next' and the VBS file runs without error. I get my message that the script is complete and then the window closes as expected. I'm trying different ways to reference the fields like [table].[field] but still not getting anything to change on the output. Is there a way to check the recordset values before I try to write them? I have a message box that shows the count and type of the recordset fields and it is the same as the working VBS file. — Chris P, Aug 21 '17 at 17:34
Types of the fields will be the same with empty data. See if execution goes inside the `do` loop. — GSerg, Aug 21 '17 at 17:41
I put a msgbox inside the do loop and it did not fire so I guess it isn't getting inside the do loop. That implies to me that the rs is at EOF so empty as GSerg is suggesting. If the query returns results inside of Access why would this query not have any results? I am definitely running the query in the same Access file that my VBS script is accessing. — Chris P, Aug 21 '17 at 18:55
I believe GSerg is correct and the query is returning an empty result. However, the query returns results if I run it inside the Access file. How can this be and what changes do I need to make to my SQL statement in the VBS file so that it returns data? I'm not seeing anything structurally different in my other query that does run correctly. — Chris P, Aug 21 '17 at 19:40
That's hard to say without knowing exactly what your query does (I see the full SQL, but it's referencing another query. To properly know what's going on, I would need its SQL too). — Erik A, Aug 21 '17 at 20:00
Can you tell me what characteristics of the query might be affecting the returned values? It is a SELECT query with one calculated field. Is there documentation somewhere I can read about what kinds of queries can be queried by an ADODB.Connection? my other VBS file queries a query without issue. — Chris P, Aug 21 '17 at 21:01
@ChrisP Any and all queries can be run by an `an ADODB.Connection`, there is no limitation other than what the database engine can understand. Change the query to `SELECT COUNT(*) c FROM ... WHERE ...` and do `MsgBox rs.Fields(0)` to show the number of selected records. I'm willing to bet it's 0, which means you need to go over your SQL syntax very carefully. — Tomalak, Aug 22 '17 at 13:03
@Tomalak I have to disagree and have some proof. Maybe it is a bug? The query in Access that I run uses the * wildcard. my VBS looks like this: `Set rs = objConn.execute("qryTest")`. qryTest in Access file looks like this: `SELECT BM_BillDetail.ComponentItemCode FROM BM_BillDetail WHERE (((BM_BillDetail.ComponentItemCode) Like "BP*"));` qryTest runs in Access but my ADODB.Connection returns an empty recordset. If I change the wildcard from `*` to `%`, the query returns nothing in Access but produces records for the ADODB object. very strange but I am unsure how ADO interacts with Access. — Chris P, Aug 22 '17 at 19:58
@GSerg had it correct from the get go. My recordset was empty and that is why `ÿþ` was being written to the .CSV. The reason why a working Access query was returning nothing from my ADODB.Connection was because the Access query used the `*` wildcard. Replacing `*` with `%` in my Access query broke it in Access but made it work for the ADODB.Connection. No idea how the ADODB.Connection interacts with Access so that the traditional SQL syntax is being used as opposed to T-SQL that Access uses. Thanks to all! — Chris P, Aug 22 '17 at 20:25
The `*` is a specialty in the Access user interface. The underlying database engine (MS JET) uses the SQL Standard form `%`. Access transparently translates this for you, but when you talk to the database engine directly, like ADO does, then you need to use the `%` directly. — Tomalak, Aug 22 '17 at 20:30
@Tomalak, Thank you for the clarification! Is this documented somewhere so that I can read up on it? Your comments have been very helpful. — Chris P, Aug 22 '17 at 21:04
My statement was based on my experience with querying Access databases in various ways over the years. Access' history is long and winding and there are many such idiosyncrasies. I've seen APIs come and go and have not done much with it anymore for at least five years. I've never bothered to look up this oddity and have always written it off as "one of those things". Turns out, somebody else has [asked the same question in 2009](https://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign) and received a much better explanation. — Tomalak, Aug 23 '17 at 01:27

Recordset writing ÿþ to CSV file instead of query results

0 Answers0