Another way to capture data

[D Willett]

I use an Access database to capture data from a dsn and populate an excel sheet. The Access side of things proves to be slow and problematic from time to time mainly due to the individual pc's where the database query has to run.
I'd like to do this another way and do away with the Access database altogether. I'm not sure which route to take to make this data capture more friendly and efficient.
I've included my code so you can see the routine:

Code: Select all

Sub RunCycleQuery()
 
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
ProgressBar.Show
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(ActiveWorkbook.Path & "\dw2.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[StartDate]") = Range("Dashboard!B3").Value
.Parameters("[EndDate]") = Range("Dashboard!B4").Value
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Range("Cycle!A1:CC10000").ClearContents
 
'Step 6: Copy the recordset to Excel
Range("Cycle!A2").CopyFromRecordset MyRecordset
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count

Sheets("Cycle").Cells(, i).Value = MyRecordset.Fields(i - 1).Name

Next i
 
End Sub

[HansV]

Hi Dave,

You mention "to capture data from a dsn". Where do these data come from?

[D Willett]

The dsn is an ODBC connection "AutoClaim" which connects very easily to Access using linked tables. I create queries in Access then run them from Excel as above.

[HansV]

It's probably possible to connect to AutoClaim directly from Excel, using DAO or ADO.
You'd have to generate the SQL dynamically - not hard, but I don't know how complicated your queries in Access are.

[D Willett]

Hi Hans
If I can capture the table as it is then the query would simply be select all from table and populate the worksheet. The calculation side of things would be done in Excel as opposed to within the Access query. The main reason I used Access is because I'm so comfortable with it. Going forward I realise I have to change things to make the project more robust.

[HansV]

If you set a reference to DAO in your Excel workbook, you can use DBEngine.OpenDatabase to open a connection using the DSN (i.e. directly to the source data, not to your Access database). Then open a recordset on the table, and use CopyFromRecordset to copy the records into a worksheet.

Alternatively, if you set a reference to ADO, you can create an ADODB.Connection object using the DSN, then open a recordset etc.

[D Willett]

Thanks Hans. This gives me a good start :-)

[D Willett]

This is working for me and copies the recordset to a sheet. Can you suggest any improvements to this? One thing is the header row isn't copied across.

Code: Select all

Sub Test()
Dim cnMTPS As ADODB.Connection
Dim cmd As ADODB.Command

Set rst = New ADODB.Recordset
Set cnMTPS = New ADODB.Connection
cnMTPS.ConnectionString = "Autoclaim"
cnMTPS.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnMTPS
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * from DBA.INVOICEVIEW"
cmd.Execute

Set rst = cmd.Execute
Sheet2.Range("A2").CopyFromRecordset (rst)
'cmd.CommandText = "DROP TABLE #ID"
'cmd.Execute
cnMTPS.Close
MsgBox "DONE"

End Sub

[HansV]

You already had code to enter the field names in row 1 in your original macro. It's basically the same here:

Code: Select all

Sub Test()
    Dim cnMTPS As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Long
    ' Open connection to database
    Set cnMTPS = New ADODB.Connection
    cnMTPS.Open ConnectionString:="Autoclaim"
    ' Open recordset on table
    Set rst = New ADODB.Recordset
    rst.Open Source:="SELECT * from DBA.INVOICEVIEW", _
        ActiveConnection:=cnMTPS, Options:=adCmdText
    ' Enter field names in first row
    For i = 1 To rst.Fields.Count
        Sheet2.Cells(, i).Value = rst.Fields(i - 1).Name
    Next i
    ' Copy records to worksheet, starting at cell A2
    Sheet2.Range("A2").CopyFromRecordset rst
    ' Close the recordset and the connection
    rst.Close
    cnMTPS.Close
    MsgBox "DONE"
End Sub

This version opens the recordset directly from the connection instead of via a separate Command object.

[D Willett]

Brilliant Thanks Hans, Would I have to clear the rows each time I run this or does this just over write existing data?

[HansV]

CopyFromRecordset overwrites the cells it needs, but you may want to clear the sheet before running the macro.
If the sheet currently has 300 rows of data, and if the code copies 250 rows into the sheet, the data in those 250 rows will be overwritten, but the 50 rows below that will be left unchanged. This could cause confusion.

You could simply insert a line

Sheet2.Cells.ClearContents

at the beginning of the macro.

[D Willett]

Yes that makes sense, I'll insert the line.
Cheers again

[D Willett]

Because I had a pivot table using the data "Sheet2.Cells.ClearContents" Excel wouldn't let me clear the sheet.
The following was allowed and works fine.

Dim RowNumber As Integer
RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row
Range("Sheet3!A1:CC0" & RowNumber).ClearContents