ms-access vb6 ms-access-2013 ms-access-97

How to copy a table's contents from an Access 97 file into an Access 2013 file

We have an VB6 application that used Ms-Access 97 database files in conjunction with a workgroup file. To keep users from accidentically opening the files directly, we have changed the extension of the files from mdb and mdw to sdb and sdw, respectively. The next Version of our application is to run with Ms-Access 2013 databasees. We also have done the extension mangling with the new accdb files, they now have the extension sccdb. Since Microsoft is discontinuing the use of old mdb files in MSO 2013 (read: MS-Access 2013 can't open the old mdb/sdb files any more, which we have to do to update data), we want to transport the data from one of the old file's tables to a table in the new accdb file (that already contains the correct table). To do that I am currently doing the following:

Const cNewFile = "C:\test\myNewAccdb.sccdb" 'new DB
Const cOldFile = "C:\test\myOldMDB.sdb"     'old DB
Const cOldMDW = "C:\test\myOldMDW.sdw"      'workgroup file of old DB, no Idea where to put this
Const cOldMDWUsr = "user" 'username and pwd for old mdw/sdw
Const cOldMDWPwd = "pass" ' also no idea where to put this

Dim dbConn As ADODB.Connection
Dim tName As String

tName = "Table1"  'name of table to work with
Set dbConn = new ADODB.Connection 'connection to new db
dbConn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & cnewfile
'remove existing data in new table
dbConn.Execute "DELETE FROM " & tName, , adExecuteNoRecords

'now the tricky part: Copy all rows from the old mdb/sdb ****************
dbConn.Execute "INSERT INTO " & tName & " SELECT * FROM " & _ 
"[MSAccess;DATABASE=" & cOldFile & ";].[" & tName & "]"
' ***********************************************************************
'this currently leads to an "could not find installable isam" error

I have no idea how to tell the open connection that the file actually is an MDB (=SDB) and that there is an MDW (=SDW) with a username and password.

Does anybody have a clue how to formulate this in an Execute(...) command? I really would like to avoid having to copy all records in big for loop using a recordset. Any help is greately appreciated.

Edit: Thank you all for you comments and hints, here is what I finally did: We are using the Microsoft Access Database Engine 2010 Redistributable as a backend (http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255) and are copying all records field by field. The behaviour is quite strange, as ADODB lets me open a renamed accdb or mdb file very nicely using the respective providers in the connectionstring (accdb/sccdb: "Microsoft.ACE.OLEDB.12.0", mdb/sdb: "Microsoft.Jet.OLEDB.4.0"), but there seems to be no way to convince a connection that is running on an accdb provider to open an mdb for the abovementioned INSERT command. So - what I do is open two connections to the two files and then shove the data across using a function like this:

Public Sub DB_Copy_Table(dbConnNew As ADODB.Connection, _
    dbConnOld As ADODB.Connection, _
    TableName As String)

    Dim f As Long
    Dim rsOld As ADODB.Recordset, rsNew As ADODB.Recordset

    On Error GoTo err_Copy
    'delete new data
    dbConnNew.Execute "DELETE FROM " & TableName, i, adExecuteNoRecords 'adExecuteNoRecords=128
    Set rsOld = New ADODB.Recordset'make a recordset for old data
    With rsOld
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .Open "select * from " & TableName, dbConnNew
    End With
    Set rsNew = New ADODB.Recordset'make a recordset into the new table
    With rsNew
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open "select * from " & TableName, dbConnNew
    End With
    If rsOld.RecordCount > 0 Then
        rsOld.MoveFirst'copy data if we have some
        Do While Not rsOld.EOF
            rsNew.AddNew
            For f = 0 To rsOld.Fields.Count - 1
                rsNew.Fields.Item(f).value = rsOld.Fields.Item(f).value
            Next
            rsNew.Update
            rsOld.MoveNext
        Loop
    End If
End Sub

Solution

Your problem is not that Access 2013 is unable to open .mdb files with User-Level Security (ULS). In fact, Access 2013 still has the options to work with .mdb files protected by ULS, including the "User-Level Security Wizard" and related utilities under "Manage Users & Permissions" on the File tab.

Your problem is that Access 2013 will not work with Access 97 database files at all, regardless of whether they are ULS-protected or not. Furthermore, it is not just a function of Microsoft Access 2013 (the application) refusing to work with Access 97 files, the corresponding version of the Access Database Engine (version 15.0) also refuses to have anything to do with Access 97 files.

So, if a machine has Access 2013 installed and your application specifies "Microsoft.ACE.OLEDB.12.0" you are actually using ACE version 15.0, not version 12.0, and you won't be able to open any Access 97 .mdb file. (For more information, see my other answer here.)

You could avoid some inconvenience by standardizing on an Access 2010 back-end (instead of Access 2013) because that version of ACE will still read Access 97 files. However, you would still need a way for users who already have Access 2013 to upgrade their old database files without having to downgrade to Access 2010.

One way to accomplish that would be to create a 32-bit conversion app that uses the older Jet database engine to convert the Access 97 file into a format that Access 2013 can use. A simple VBScript example would be something like this ...

Option Explicit
Const dbLangGeneral = ";LANGID=0x0409;CP=1252;COUNTRY=0"
Const dbVersion40 = 64 
Const dbFailOnError = 128

Dim dbe, con
Dim sourceDbSpec, mdwSpec, destinationDbSpec, uid, pwd, tableName

sourceDbSpec = "Z:\_test\a97file.mdb"
mdwSpec = "Z:\_test\Security.mdw"
destinationDbSpec = "Z:\_test\converted.mdb"
uid = "Gord"
pwd = "whatever"
tableName = "Table1"

Set con = CreateObject("ADODB.Connection")
con.Open _
        "Driver={Microsoft Access Driver (*.mdb)}" & _
        ";Dbq=" & sourceDbSpec & _
        ";SystemDB=" & mdwSpec & _
        ";Uid=" & uid & _
        ";Pwd=" & pwd

' create target .mdb file
Set dbe = CreateObject("DAO.DBEngine.36")
dbe.CreateDatabase destinationDbSpec, dbLangGeneral, dbVersion40
Set dbe = Nothing

' copy table
con.Execute _
        "SELECT * " & _
        "INTO [;Database=" & destinationDbSpec & "].[" & tableName & "] " & _
        "FROM [" & tableName & "]", _
        dbFailOnError
con.Close
Set con = Nothing

... which will create an Access 2003 .mdb file. That file can then in turn be imported into your .accdb file via the Access 2013 version of the Access Database Engine.