Upgrade to Access2007 Reference Problem

[KTuohy]

:banghead:My database went from 97-2000-2003-2007 in the hope that the References would work once it got to 2007. Alas, that's not working at all. I checked the 2007 References and they match with the original 97 db but I'm still getting an error.

When I try to open a form it's giving me:
Run-time error '13':
Type mismatch

Then it highlights:
Set rst = dbs.OpenRecordset(strSQL)

The whole code is:

Dim dbs As database
Dim rst As Recordset
Dim strSQL As String
Dim my_local_sw_version As String
Dim my_local_created_time As String

Set dbs = CurrentDb()

strSQL = "SELECT * FROM [Local SW Version]"
strSQL = strSQL & " Order by [ID];"
Set rst = dbs.OpenRecordset(strSQL)

I'm really frustrated and I'm hoping y'all can assist me with this.

Thank you.

 

[namliam]

try disabiguating

Dim dbs As dao.database
Dim rst As dao.Recordset

 

[KTuohy]

Yoo Hoo, it worked! Oh, what a relief! Thank you, thank you, thank you!

 

[namliam]

Which means you have both the DAO and ADO references added... Generaly one only uses one of these... Mixing them is generaly ill adviced.

Also Disambiguating, making sure you pick the right version of anything that can be missinterperted is a *verry good practice*
Friendly advice, Always disambiguate things like Database and Recordset

 

[KTuohy]

Thank you again. I will look into choosing DAO or ADO. Do you have a preference? Both ran just fine on 97 but we're not using that any more, Thank God!

Do you have a suggestion for which one works best in 2007?

Thanks again.

 

[namliam]

I dont remember 97 offhand, but access itself when working with forms and reports uses DAO.

For this reason most of the times people use DAO inside the database.
ADO really can be helpfull if you need to do stuff on a bigger backend like Oracle or SQL Server

 

[KTuohy]

That does explain it then. We use SQL tables. So I guess I fuddle through with DAO and ADO.

One thing interesting it's creating Functions all over the place in 2007! Functions I have no idea what they do or why they are suddenly there.

Is that "normal"?

 

[namliam]

Sorry no clue

 

[namliam]

You may have been Lucky all this time, the order of references is what matters.
In your old db you probably have DAO as the top dog, over the ADO one. (higher in the list)
While in the new one you have inadvertenately reversed that order.

 

[KTuohy]

You are probably right about being lucky! I was drafted into this position because I'm good with pulling reports. This whole database was a dump job on me. But it has had it's up side.

Thank you again.