Tracy-ann has attended:
Excel VBA Advanced course
Importing information from Access to Excel
When running my code I keep getting either an automation error or an object defined error and am not able to figure out what I am doing wrong. The call recordset is where i get the message so it it either connection string related or recordset related. Also the copyfromRecordset method does work ( I also get an error here).Please see code below up to where problem occurs:
Public Sub GetData()
'Connection string equal to a constant
Const connectionstring As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=K:\QVC Database.mdb;Persist Security Info=False"
Const sSQL As String = "Select * From Gross Sales Query1"
Dim connection As ADODB.connection
Dim recordset As ADODB.recordset
'Set connection = New ADODB.connection
'connection.connectionstring = connectionstring
'connection.Open
'open the recordset
Set recordset = New ADODB.recordset
Call recordset.Open(sSQL, connection)
RE: Importing information from Access to Excel
Hello Tracy-ann
Thank you for your question and welcome to the forum.
If you can email the relevant spreadsheet to stephen@stl-training.co.uk then Stephen will endeavour to reply to your post before Monday.
Kind regards
Amanda
RE: Importing information from Access to Excel
Hi Tracy-Ann
Thank you for your question
Could you tell me the precise wording of the error messages that you are receiving?
A couple of initial thoughts for you to check
I notice that in ADODB.recordset and ADODB.connection the r and the c are not capitalised. This suggests that the ADODB object references are not loaded. If you go to tools-references in the vbe, check if microsoft activeX object library is selected. If not scroll down and tick it.
Another possible problem is the fact that your query name "Gross Sales Query"has spaces in it. This is likely to cause problems . If possible remove the spaces in the query name in access and in your constant, thus GrossSalesQuery.
If the above doesn't solve the problem please get back to me with the exact wording of the error messages. It might also be useful if you could email me a copy of the actual workbook, so I can investigate further
Regards
Stephen
