automatically query and convert access db table to excel using vbs

I have an Access database. I would like to automatically query the table Data_01 and export the results to an Excel spreadsheet using ADO in VBScript on a daily basis. At present my skills in ADO are lacking.

1. I have a date time column that I would select items from yesterday and today. In a GUI query the criteria would be Between Date() And Date()-1
2. I have a PartNumber column that I would like select a specific part number. In a GUI query the criteria would be Series 400
3. I would then like to select other columns based on the criteria in items 1. and 2.
4. I would like to get the header row for the columns also.

I am presently exporting the entire table to Excel and then using a VBScript to select the columns that I want and then deleting all unwanted data, then auto-fitting the columns for my final output file. This appears to be somewhat processor- and time intensive.

From stackoverflow

• Have you tried the built in functions in Excel for importing data? I don't have a English language version of Excel, so I won't guide you to them, but I think the menu is called "Data".

• My first reaction is to do the following:

  1. Create a query object in MS Access that finds the data you want to export [Database Window -> Queries -> New (use the GUI builder for now)]
  2. Create a macro that exports the query to an Excel file. I talk more about that here. You could do this in VBA as well... many would say that was more "pure" (I have macros as well); but whatever floats your boat.
  3. Set up an autoexec macro (this will run automatically when the MS Access opens) that runs the export macro you just created and then exits MS Access (you can override this my holding down the shift key while Access is loading). It would be slightly better to also create a separate MS Access file to preform these operations without affecting the original MS Access file, just by creating table links to the original.
  4. Set up a Scheduled Task to open the MS Access file once a day.
  CodeSlave : That's simply an issue of scale and scripting the harvesting of data from all 30 AMMs.

• Here is some sample VBScript

  Dim cn 
  Dim rs
  
  strFile = "C:\Docs\LTD.mdb"
  
  strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
  
  Set cn = CreateObject("ADODB.Connection")
  Set rs = CreateObject("ADODB.Recordset")
  
  cn.Open strCon
  
  strSQL = "SELECT * FROM tblTable " _
  & "WHERE CrDate Between Now() And Date()-1 " _
  & "AND OtherField='abc' " _
  & "AND PartNumber=1 " _
  & "ORDER BY CrDate, PartNumber"
  
  rs.Open strSQL, cn
  
  Set xl = CreateObject("Excel.Application")
  Set xlBk = xl.Workbooks.Add
  
  With xlbk.Worksheets(1)
      For i = 0 To rs.Fields.Count - 1
          .Cells(1, i + 1) = rs.Fields(i).Name
      Next
  
      .Cells(2, 1).CopyFromRecordset rs
      .Columns("B:B").NumberFormat = "m/d/yy h:mm"
  End With
  
  xl.Visible=True
  

• Remou,

  Your answer looks very promising. I will edit the code to match my situation, and give it a try. I hope this is the correct way to respond. My first time posting here so klutzing around a little bit. Thanks for your answer.

  Nat

  Tester101,Remou

  I tried to reply by adding a comment to your answer, but not enough reputation points to use that method. Tough being the newbie. Thank you also for your answer, also a promising solution. I have done quite a bit of wmi and wsh scripting for admin purposes, but this is my first time being tasked to deal with databases. I knew ADO was the right solution but shortly after looking at it my eyes glazed over :-) Lots for me to learn. Thanks for opening the door with some real world solutions.

  Nat

  Svinto,

  I am looking at the data function for automated charting of the data, which is another portion of the project. Thanks for your response.

  Codeslave,

  When I was first starting this project I actually did exactly as you suggested building a query and a macro and was going to script the execution of the macro. Very fast and works very nicely. Unfortunatly due to the number of machines that I am dealing with and the variations in the databases, available time on the machines, and some other contraints it became problematic. Thanks for your response, and comments. Again my apologies for not using the commenting function still not enough points.

  Tester101,

  Thanks for the csv output code. I do like csv files in many instances. Just about anything will read them nicely (ASCII) :-). Still can't use comment function :-(

  Nat

  Remou : Hi, thanks. In Stackoverflow, comments are used for replies that are not answers to the question. Like this comment.

• If you don't have Excel you can access an xls with ADO like this
  

  
  Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = &H0001
  Const strDB = "" 'Location of Database file
  Const strXLS = "" 'Location of spreadsheet
  
  
  Set objAccessConnection = CreateObject("ADODB.Connection")
  objAccessConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strDB
  Set objExcelConnection = CreateObject("ADODB.Connection")
  objExcelConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strXLS & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
  Set objAccessRecordset = CreateObject("ADODB.Recordset")
  Set objExcelRecordSet = CreateObject("ADODB.Recordset")
  
  strAccessQuery = "SELECT * FROM Data_01 WHERE PartNumberColumn = 'Series 400' AND DateColumn BETWEEN #" & Date -1 & "# AND #" & Date & "#"
  objAccessRecordset.Open strAccessQuery, objAccessConnection, adOpenStatic, adLockOptimistic
  
  strTable = "Sheet1$"
  objExcelRecordSet.Open "Select * FROM [" & strTable & "]", objExcelConnection, adOpenStatic, adLockOptimistic, adCmdText
  
  Do Until objAccessRecordset.EOF
     objExcelRecordSet.AddNew
     For i = 0 To objAccessRecordSet.Fields.Count - 1
         objExcelRecordset.Fields(i).Value = objAccessRecordset.Fields(i).Value
     Next
     objExcelRecordSet.Update
     objAccessRecordset.MoveNext
  Loop
  
  objExcelRecordset.Close
  Set objExcelRecordset = Nothing
  objAccessRecordset.Close
  Set objAccessRecordset = Nothing
  objAccessConnection.Close
  Set objAccessConnection = Nothing
  

  The only thing to watch out for is to make sure the columns in the spreadsheet have a title in the first row, otherwise this script could fail.

  EDIT:
  you could also write the recordset to a .csv file.

  
  Const adClipString = 2
  Const ForWriting = 2
  Const ForAppending = 8
  Const strDB = "C:\Test.mdb"
  Const strCSV = "C:\Test.csv"
  
  
  Set objAccessConnection = CreateObject("ADODB.Connection")
  objAccessConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strDB
  
  Set objAccessRecordset = CreateObject("ADODB.Recordset")
  
  strAccessQuery = "SELECT * FROM Data_01 WHERE PartNumber = 'Series 400' AND TheDate BETWEEN #" & Date -1 & "# AND #" & Date & "#"
  objAccessRecordset.Open strAccessQuery, objAccessConnection, adOpenStatic, adLockOptimistic
  
  Set objCSV = CreateObject("Scripting.FileSystemObject").OpenTextFile(strCSV, ForAppending, True)
  objCSV.Write objAccessRecordset.GetString(adClipString,,",",CRLF)
  
  objCSV.Close
  Set objCSV = Nothing
  objAccessRecordset.Close
  Set objAccessRecordset = Nothing
  objAccessConnection.Close
  Set objAccessConnection = Nothing
  

  Excel will open .csv files with no problem. The downside of this method is Excel does not do well with saving .csv files, but in excel the csv file can be saved as an xls.