How do I code VB6 with multi-versions of MS Access (2010, 2000, 97, ...)

[anderci]

I routinely develop self sufficient (stand alone) VB6 applications to create, modify and query MS Access 2000 databases. (referencing MSACC9.OLB and dao360.dll)
This includes creating the tables, relationships and query definitions.

If I replace dao360.dll with acedao.dll from http://originaldll.com/file/acedao.dll/12185.html , can I CreateDatabase and CompactDatabase , ... specifying dbVersion120 / dbVersion40 / dbVersion30 ... ( Access2010, 2007, 2003, 2000, ...)

Do I also need to find a MSACC??.OLB file? from where?

[jggtz]

Search about ADOX

[anderci]

Thank you for responding.
I am using DAO , not ADO .
I am seeking to extend my applications to also work with Microsoft's newer databases with minor changes to cope with newer filename extensions, to detect which, if any, version of MS Access is installed on the user's PC and to allow the user to select which version of database to create.

Some of my non-profit clients have no databases installed and some are using MS Access 2010.

Right now, all of my applications default to MS Access 2000. That is the version I use and will continue to use every day.

[anderci]

I have had a hint of success:
I made a separate copy of one of my smaller VB6 projects that creates a database.
I down loaded a copy of acedao.dll from http://originaldll.com/file/acedao.dll/12185.html
I pug a copy of the acedao.dll file inside the new project folder.
I open this new project in Visual Studio and opened menu item Project/References...:
I unchecked Microsoft DAO 3.6 Object Library dao360.dll
I also unchecked Miocrosoft Access 9.0 Object Library MSACC9.OLB
I clicked the [Browse...] button and located the acedao.dll file in the project directory.
I checked this Microsoft Office 14.0 Access database engine Object acedao.dll
The addition of reference acedao.dll adds new variables to the project: dbVersion120 and dbVersion140.
Compiling and running this revised program works until I Set dbNew = CreateDatabase(...
I get the error message: 429 ActiveX component can't create object.
I installed MS Access 2010 on another PC and copied the ...\Office14\msacc.olb file and placed a copy in the new project folder.
I tried the Project/References... menu item again and used the [Browse...] button to locate and open the local copy of the msacc.olb file.
I cannot find the msacc.olb ~ Microsoft Access 14.0 Object Library in the list of Available References.
Any ideas, Please!

[anderci]

I tried a copy of msacc.olb (for MS Access 2010 - 14.0) from another source.

I stepped through the long list of Available References for the msacc.olb file I had placed in the project directory.

Why is the msacc.olb not available as a Reference?

[jggtz]

Try clicking over the Examine button
...also
when I posted that you search about ADOX I meant that you "create, modify and query MS Access 2000 databases" using DAO but to "create the tables, relationships and query definitions" use ADOX

[anderci]

jggtz, Thank you for responding.
Where would I find the Examine button?
( I am using WinXP sp3)

I have been doing it all with MSAccess 2000 MSACC9.OLB and MSAccess 2000 dao360.dll
Here are some example lines of VB6 code that I want to be able to use with any version of MS Access (2010, 2000, 97, ...):

Dim dbNew As DAO.Database
Dim tdfNew As DAO.TableDef
Dim fdfNew As DAO.Field
Dim idx As DAO.Index
Dim relNew As Relation
Dim qdfNew As QueryDef
Dim qdfs As QueryDefs
Dim rels As Relations
Dim rst As DAO.Recordset
Dim dbTablDef As DAO.TableDef
Set dbNew = DAO.OpenDatabase(sDbName)
dbNew.Execute qtdLUQuantity
Set tdfNew = dbNew.TableDefs("LUQuantity")
Set fdfNew = tdfNew.Fields("QuantityID")
For Each tdfNew In dbNew.TableDefs
For Each fdfNew In tdfNew.Fields
For Each idx In tdfNew.Indexes
For Each fdfNew In idx.Fields
Set relNew = dbNew.CreateRelation("LUSymptomLevellSymptomTimeSymptomd")
relNew.Table = "LUSymptomLevel"
relNew.ForeignTable = "SymptomTimeSymptom"
Set fdfNew = relNew.CreateField("SymptomLevel")
fdfNew.ForeignName = "SymptomLevel"
relNew.Fields.Append fdfNew
relNew.Attributes = dbRelationRight + dbRelationUpdateCascade + dbRelationDeleteCascade
dbNew.Relations.Append relNew
dbNew.Relations.Refresh
Set qdfNew = dbNew.CreateQueryDef("qMealTime", qMealTime)
Set qdfs = dbNew.QueryDefs
Set rels = dbNew.Relations
Set rst = dbNew.OpenRecordset("SELECT * FROM " & tblName, dbOpenSnapshot)
dbNew.Execute SQLUpdate, dbSeeChanges

[jggtz]

Where would I find the Examine button?

Maybe it is the Browse button
(My VB6 ide is not in English)
...anyway it's a button on the right side of the references dialog box that is suited for you to search any other library that it is not included in the list

lines of VB6 code that I want to be able to use with any version of MS Access (2010, 2000, 97, ...):

IMO you cann't do it using DAO... read a little about ADOX

[dilettante]

DAO comes in different versions for different versions of Jet MDBs.

Using ADO/ADOX along with Jet OLEDB Providers gives you more flexibility. The Jet 4.0 Provider should work with any MDB in Jet formats from JET_ENGINETYPE_JET4X down to JET_ENGINETYPE_JET10 according to the docs. ACE Providers are needed for ACCDBs but also handle the Jet MDB types. However every new version of Access you get a new ACE Provider.

People use ACCBDs so seldom that there isn't much info on ACE Provider downward compatibility. They just don't offer enough of an advatange to see much use. Doubly so since Jet 4.0 is preinstalled as part of Windows, eliminating an extra deployment headache.


Unless you are automating instances of Access (a sucker's game at the best of times) you shouldn't even care about MSACCx.OLB. And if you are then you shouldn't be using either DAO or ADO in your VB6 program.

Sounds like you have quite the rat's nest there.

[anderci]

Thank you for your comments.

[anderci]

I looked at ADOX, some. The code samples related to creating databases look similar to the DAO code I have used extensively in my several applications. I do create and / or work with multiple databases. I do not connect any databases directly to any form controls. My NSIS install processes are reg.free.

If there truly is not a deployable msacc.olb for MS Access 2010, then I will probably give up on extending my freeware applications to MS Access 2010.

I have ducked migrating my VB6 code (with features like infinitely scrolling data entry forms) to VB.NET. Why should I migrate to yet another data access method?

Here is an article written by someone with far more experience than I:
http://blogs.msdn.com/b/michkap/arch...3/3849288.aspx

Again, thank you for your responses.