Hello all, I have a very basic application ('Console') in VB6 which launches various documents and shortcuts. All works fine with Office 2003, but on an Office 2007 machine, when I click on the button to launch an Excel document (in an IE window, as it happens) the document opens fine, but...
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.
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.
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.
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.
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
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!
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?!
Microsoft Office 2019 Professional For Windows PC Microsoft Office Professional 2019 provides the essentials to get it all done. Ideal for small businesses and families who need the most popular Microsoft Office apps including Word, Excel, PowerPoint, OneNote and importantly Publisher, Access...
support.sas.com
fastsoftware.co.uk
