Using VBA, ADO with SQL to Add Append Query results to Excel 2007 Table

[Danielle.Tx]

Hello Everyone,

I have been working on this for about a week and have finally decided to try for help. I am new to using VBA to get things done.

I have successfully used VBA to populate select query results into an excel worksheet on open, for a co-worker. Now I am trying to populate the records from an append query to the bottom of those results, which are now in a table on an excel spreadsheet, Contractor EIF. I am trying to make this work because my co-worker modifies the results in the table, deleting rows, adding fill color etc. and as new projects begin he would like those added to the projects already in the table (without rewriting the entire table).

This is what I have:

Private Sub Window_Open()


Dim strDB As String
Dim strMyPath As String
Dim strDBName As String


'data source with which to establish connection - MS Access DB Name

strDBName = "MyCopy TD_1T.accdb"

'Get Path / location of DB
strMyPath = "C:\Users\IL11111\Desktop\Documents\TurnDown DB"

'Set the string variable to the DB
strDB = strMyPath & "\" & strDBName

Dim connDB As New ADODB.Connection

'Open connection to the TD DB
connDB.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB

'Execute SQL Statement
connDB.Execute "INSERT INTO [Contractor EIF$] IN '' [Excel 12.0;Database=" & MyPath & "\Contractor EIF.xlsm] SELECT * FROM qry_Append_Contractor_EIF"

'close the connection
connDB.Close

'get rid of variables
Set connDB = Nothing

End Sub

When I use f8 to step through the code on the line beginning, conn.DB.Execute (after I select OK the cursor is set at the beginning of this line), I get an error message that reads:

Run-time error '-2147467259 (80004005)':
Automation Error
Unspecified Error

References I have set are:
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ActiveX Data Objects Library
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Access database engine Object
Microsoft ADO Ext. 6.0 for DDL and Security
Microsoft Scripting Runtime
Microsoft OLAP Designer Server Driver 8.0
Microsoft OLE DB provider for OLAP Services connection dialog 8.0
Microsoft ActiveX Data Objects 6.1 Library

Thanks for any help, or if someone could point me in the right direction it would be much appreciated.

 

[Uncle Gizmo]

I found this thread HERE:- Manipulating XL via VBA very helpful when putting data into spreadsheets from MS Access.

I didn't do any appending of data though.

A big guess here:- but I'm thinking out loud as it were, the issue is how to find the last record in the Excel sheet?

Idea's
1) Use MS Access VBA to look in your sheet and find the last row. I suspect it's possible, to find out I would search or ask in an Excel forum.

2) When you transfer the data you will know the position of he last row, so record it ready for next time. A problem with this idea is that if the user adds info on the end, that you now write over.

 

[thechazm]

Simple example to find the last row in an excel sheet:

xlsSheet.Cells(xlsSheet.Rows.Count, "A").End(xlUp).Row

Just replace the column letter above and the sheet name to either active sheet or some declared sheet name.