Trouble opening an Older version of Access and creating a record set.

[JB510]

I am using the latest version of MS Access, and trying to open an older version of Access and create a record set but I get an error.
Stepping though my code, I get an error when I try to set the query:
Set rs = db.OpenRecordset("SELECT * FROM RMA_Serial_Numbers WHERE Customer_SerialNum = '" & SN & "'")
it will give a Run-time error '91': Object variable or with block variable not set. What am I doing wrong??

Below is the code so far..
Dim accessApp As Object ' Late binding - allows using different Access versions
Dim db As Object
Dim rs As Object
Dim strSQL As String
Dim SN As String
Dim Clientele As String



Clientele = "C:\Path\to\ctel.mdb"

' Create an instance of MS Access application
Set accessApp = CreateObject("Access.Application")

' Open the old version of Access database
accessApp.OpenCurrentDatabase Clientele

' Show the Access window
accessApp.Visible = True

' Get reference to the opened database
Set db = accessApp.CurrentDb

'Open recordset
Set rs = db.OpenRecordset("SELECT * FROM RMA_Serial_Numbers WHERE Customer_SerialNum = '" & SN & "'")

[Welshgasman]

Not posting within code tags to start with.

have you checked the local variables?, are they set correctly?

[orange]

Do you get any details with that error?
Have you tried stepping through your code to identify the line where error occurs?

??Where in Ontario??

[Welshgasman]

Code works for me, in that it runs the query. However I am only on 2007.

Sub TestAnotherDB()
Dim accessApp As Object ' Late binding - allows using different Access versions
Dim db As Object
Dim rs As Object
Dim strSQL As String
Dim SN As String
Dim Clientele As String


Clientele = "F:\Users\Paul\Documents\Bibbys.mdb"


' Create an instance of MS Access application
Set accessApp = CreateObject("Access.Application")


' Open the old version of Access database
accessApp.OpenCurrentDatabase Clientele


' Show the Access window
accessApp.Visible = True


' Get reference to the opened database
Set db = accessApp.CurrentDb


'Open recordset
Set rs = db.OpenRecordset("SELECT * FROM Crew WHERE ID = 23")


End Sub

[orange]

Hi Paul,

Glad it worked ok for you on 2007. Perhaps OP/JB510 could tell us in what version his .mdb was created.

[Welshgasman]

My mdb would be from 2003 Access.

[JB510]

Sorry about posting the code. I'm new at this forum and will look up how to properly..

The old database was running with Access 2003.
I am currently using the latest with Office 365 to try to just read the info from the old database.

I am stepping though the code with the F8 key and it errors out on that line.
I know the variables in the "SELECT * FROM RMA_Serial_Numbers WHERE Customer_SerialNum = '" & SN & "'" command should work as it currently is working on another machine running an older version of Excel in a XP emulator. (Thus I am creating a new database program to do alot of little jobs I had excel doing.)

I am working in Guelph ON but live in Hamilton ON.

[JB510]

I had multiple programs I run in an XP emulator on another machine.
old 2003 Excel programs and MSAcess.

I am making a new Office 365 MS Access database but still need the old info for old record pulling.
I am currently trying to read a 2003 MS Accces .xls file using similar code but am running into runtime errors.

[Welshgasman]

I would be checking your objects have been set correctly?
I would likely just import the old data into the new db, then remove when no longer required.

[Micron]

I'm thinking that CurrentDb is a copy of the db that is related to the User Interface (UI), in which case that would be the db where you're running the code from, which shouldn't work. Perhaps Set db = accessApp.dbEngine(0)(0) would work for this?
I also wonder if there aren't easier ways, such as linking or importing the tables, r TransferSpreadsheet method between db's.

As for code tags, it's the # button on the posting toolbar.

[JB510]

I tried

Set db = accessApp.dbEngine(0)(0) would work for this?

it now gives Run-time Error 3265 Item not found in this collection.

It has something to do with setting db.. With the original code

Set db = accessApp.CurrentDb

it did not compain about the line after stepping though it, but if you hovered over it would say db - Nothing
..

Oh and I did some research and the old database was 1997. (.mdb)
The file is currently still being used so I can't transfer the files over.. (They are using it in an XP window for now with a 2003 version of MSAcces.)

[Welshgasman]

it did not compain about the line after stepping though it, but if you hovered over it would say db - Nothing

That is why I said 'inspect your variables/check your objects'
As I mentioned your first code shown, worked for me.

[Micron]

You cannot directly open 97 versions with 365, whatever that would be (likely 2016 or later) so I doubt you can do it programmatically either. If you have a version between 2000 and (I think) 2013 you should backup then convert these files before you can't open them at all.