98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Importing information from Access to Excel | Excel forum
Importing information from Access to Excel | Excel forum
Resolved · Urgent Priority · Version 2003
Fri 12 Jun 2009, 09:59Delegate Tracy-ann said...
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)
Fri 12 Jun 2009, 10:27Trainer Amanda said...
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
Fri 12 Jun 2009, 15:24Trainer Stephen said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Manually rotating a 3D chartTo manually rotate a 3D chart; |
