Linking to later version

[Guest]

I suspect this is an Access 2007 issue. I have an application in Access 2000
which links to another database. As part of implementing an upgrade, I
created an Access program to modify the existing Access 2000 back end. The
program modifies a few fields, adds a few new fields and tables, and exports
the result as a new back end.

One user who has Access 2007 ran the back end upgrade and when they tried to
link tables received the message.

"Microsoft Office Access does not support linking to an Access Database or
Excel Office Workbook saved in a format that is a later version then the
current database format."

Is this something new in 2007 as I have been using the upgrade approach for
a number of years without a problem? Any suggestions as to how I can rectify
it without creating a separate 2007 version?

 

[Allen Browne]

Access 2007 has a new file format (ACCDB.) It can also work with the Access
2000 MDB or the Access 2002/2003 MDB.

So, if your user has an A2000 MDB, A2007 would be fine with that.
However, if they have an ACCDB file, the A2000 file will not be able to open
it.

 

[Guest]

Hi Allen
The front end is a 2000 mde. The program that does the mods to the tables
is a 2003 mdb. The original tables are 2003 mdb which are imported into the
conversion program then exported to a new file. I am guessing Access 2007
would export them as Access 2000 format???

 

[Allen Browne]

A2007 is exporting to a new file? How do you create that file?

Here's an example of CreateDatabase():
http://allenbrowne.com/func-DAO.html#CreateDatabaseDAO

You should be able to add dbVersion40 to get it to create the MDB format.

 

[Guest]

Hi Allen

Code is:

Sub subCreateNewTable()
Dim dbNew As Database ' The new
database object

Set ws = DBEngine.Workspaces(0) ' Create workspace
strNewTables = Forms!frmUpgrade.txtPath & "PA5_Data_Tables.mdb" '
The file to export to

If Dir(strNewTables) <> "" Then 'Make sure there
isn't already a file with that name
Kill strNewTables
End If

Set dbNew = ws.CreateDatabase(strNewTables, dbLangGeneral) ' Create
the database

' Identify records to export
Set dbs = CurrentDb ' Nominate the
database
strSQL = "SELECT DISTINCT MSysObjects.Name, MSysObjects.Type " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name) Not Like ""msys*"") AND
((MSysObjects.Type)=1));"
Set rst = dbs.OpenRecordset(strSQL) ' Recordset of
table names

Do While Not rst.EOF ' Export each
table (rst!Name is the table name)

If Left(rst!Name, 1) = "t" Then ' Only export
tables starting with tbl....
subDisplayProgress "Exporting " & (rst!Name) & _
" to PA5_Data_Tables.mdb" ' Display
progress

DoCmd.TransferDatabase acExport, "Microsoft Access", _
strNewTables, acTable, rst!Name, rst!Name, False
End If
rst.MoveNext

Loop

subHideProgress ' Hide the
progress label

Set dbs = Nothing ' Clean up
Set dbsNew = Nothing
Set rst = Nothing

End Sub

 

[Allen Browne]

So did you try adding the dbVersion40 to the CreateDatabase line as
suggested?

 

[Guest]

Have made the changes and am waiting for my South African client to come
online and test it. Will keep you posted Allen. Thanks again from the other
side of the continent.

 

[Guest]

Thanks again Allen. It worked. One day I will get to Perth and buy you a
well earned beer.