error trying to reference Access from Excel (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 21:57
Joined
Sep 21, 2011
Messages
14,047
By the way, I changed the DBPath to the location and name of my database, but didn't change anything else. Should I have done?
No, I did not change anything else.

Best start googling that error.?

Here is one
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
I spotted my error now, I had left a space before the name of the file! The code has now gone as far as rec.Open query, conn. This gives an error, but I'm guessing this is because it's referring to a customer field, and I don't have any customer fields in my database?

Thanks very much for your help so far. Sorry I'm so clumsy! I'm sure I'll be back with more question very soon.

I'm still not sure why my first code doesn't work though.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738
Did 2007 come in 64bit?
No idea. I was just trying to get down to if they had the driver at all. But it seems the problem has been solved now, kind of.

The code has now gone as far as rec.Open query, conn.
Can you post the full code you are using now that currently errs on that line?
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
I now get Run-time error '-2147217865 (80040e37)': Automation error

But I changed one line to query = "SELECT * from T_MusicianDetails;", as T_MusicianDetails is the name of one of the tables in my database.
 

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738
Out of curiosity, what is your end goal? There may be an easier way?
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
Out of curiosity, what is your end goal? There may be an easier way?

Well, on my spreadsheet, I will enter some musicians codes in the second column. Based on these codes, I will get the corresponding musicians details from Access, and enter them onto the spreadsheet. All the details can be found in just one table in Access.

At the moment I reference another excel workbook, using vlookup. But I want to reference Access instead ideally.
 

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738
Is it a one to one match between a given musician code and the corresponding record with more details you want to retrieve?

You might also try something like this sample code, which takes a range (like a2:a10), reads the codes, and puts corresponding details (name and recordlabel) on the cells 2 columns over. Could either adjust to suit or completely re-tool if desired.

Code:
Sub GetDetails(rngSource As Range)
'call function like:
'   GetDetails ThisWorkbook.Worksheets("Sheet1").Range("A2:A10")
On Error GoTo errhandler
Dim rng As Range, objAccess As Object, db As Object, rs As Object, dbPath As String
Dim strMusicianName As String, strMusicianLabel As String, blAccessIsOpen As Boolean

'adjust to suit:
dbPath = "c:\users\username\desktop\Testing 20200716.accdb"
         
Set objAccess = CreateObject("Access.Application")
blAccessIsOpen = True
objAccess.opencurrentdatabase (dbPath)
Set db = objAccess.currentdb
    
For Each rng In rngSource
    Set rs = db.openrecordset("select * from tblMusicians where MusicianCode='" & rng.Value & "'")
    If rs.EOF = False Then
        strMusicianName = ("" & rs.Fields("MusicianName"))
        strMusicianLabel = ("" & rs.Fields("MusicianLabel"))
    Else
        strMusicianName = "Record Not Found"
        strMusicianLabel = "Record Not Found"
    End If
    rng.Offset(0, 2).Value = strMusicianName
    rng.Offset(0, 3).Value = strMusicianLabel
    rs.Close
    Set rs = Nothing
Next rng

objAccess.closecurrentdatabase
objAccess.Quit

Exit Sub
errhandler:
MsgBox Err.Description
If blAccessIsOpen = True Then
    objAccess.Quit
End If
End Sub

A few notes. I should correct my "an easier way" comment. I am not necessarily stating one is easier than the other--just one of those "I'm not sure why you're having that problem but here's another way" situations.

Note various places in my code where you may have to change things.
- path to db file
- table name
- string variables (change names, add more, subtract)
- recordset might open faster if you select only needed columns, I did * for the sake of simplicity
- in my example, all columns in the recordset's Where clause were Text. If you are retrieving numbers or dates and they are in the Where clause, you'll need to adjust the delimiter from a single quote to [nothing] for numbers, [#] for dates.

As I expect you'll be disappointed in the loss of speed (compared to vlookup in Excel!), another method is to return an entire multi-record recordset containing all records where MusicianID In(value1,value2,value3), dump it on another Excel sheet, then do vlookups, all in code. Just another thought.

HTH.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738
Made a slight edit to code posted, to account for record not found
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
Many thanks Isaac, I'll give that a go and let you know how I get on.

That's correct, it's a one to one match. Each code represents a different musician.

That other method you mentioned is effectively what I'm doing now, except that I'm manually exporting the data from Access to Excel, whereas it would be good to get VBA to do it for me, if possible.
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
Ok, so I've tried to run it. A Macros box pops up, asking me for the macro name from the list of VBA programmes. Is that supposed to happen?
 

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738
Ok, so I've tried to run it. A Macros box pops up, asking me for the macro name from the list of VBA programmes. Is that supposed to happen?

1. How precisely are you running it? Like how are you actually telling it to run, in a module using F5, in the immediate window, etc.
2. Also, does this error happen on a specific line of code, does the code actually go into Break mode and yellow highlight a specific line, or does that message come up as soon as you execute it.
3. Can you screenshot that popup message and as much background of the code window as possible?
4. Is Access installed on that machine?

Usually that pop up is because you're not initiating the specific procedure as you might be intending to
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
I'm running it by pressing the play button on the top of the code window. Normally I might press F8 to go through it in steps, but it's not letting me do that this time.

I'm not sure how to screenshot. Also, it's not an error message as such. It's just asking me which macro to select from. This has never happened to me before!
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
1594926284811.png
 

Isaac

Lifelong Learner
Local time
Today, 14:57
Joined
Mar 14, 2017
Messages
8,738

This is not a procedure that you can just put your cursor in and Run. Because it takes a required parameter (rngSource).

You can create a new procedure and run it like this:
Code:
Sub Test()
GetDetails ThisWorkbook.Worksheets("Sheet1").Range("A2:A7")
End Sub
Or, you can View > Immediate Window, and type in only GetDetails ThisWorkbook.Worksheets("Sheet1").Range("A2:A7"), and hit Enter
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
This is not a procedure that you can just put your cursor in and Run. Because it takes a required parameter (rngSource).

You can create a new procedure and run it like this:
Code:
Sub Test()
GetDetails ThisWorkbook.Worksheets("Sheet1").Range("A2:A7")
End Sub
Or, you can View > Immediate Window, and type in only GetDetails ThisWorkbook.Worksheets("Sheet1").Range("A2:A7"), and hit Enter



I see. This is going to take a while to get my head around! I'll have another go tomorrow. Many thanks for help today.
 

neilsolaris

Member
Local time
Today, 21:57
Joined
Apr 19, 2020
Messages
114
The code also works for me. Do you have 64 bit office?
I found this page with instructions to check if the 64 bit ACE components are installed.

Sorry, I only just checked the link you sent me before. I think this is the problem actually, I have a 32 bit office. That must be why it's not working for me. I guess I need to install something else to make it work?

I should probably upgrade my Office. Does anyone have a link to a reliable place where I can purchase it? I use Word, Excel, Outlook and Access. I found this link, which has a lifetime license for £39.99. Does that seem suspiciously cheap?!

 

Users who are viewing this thread

Top Bottom