import tables in access runtime

Question

We are distributing an access application where many users may be accessing the application via access runtime.

We wanted to give users the option to import their data from an older version of the application to the new version. We are able to do this successfully if the user has a full microsoft access version, but we can't figure out which command to use for this to work with Access runtime. We tried the following:

• in VBA: DoCmd.RunCommand acCmdImportAttachAccess -> works in full access, doesn't work in runtime
• in custom ribbon: <control idMso="ImportAccess" /> -> works in full access, doesn't work in runtime
• in macro: including the RunMenuCommand ImportAttachAccess -> works in full access, doesn't work in runtime

Is there a way to allow a user to import tables from another database of their choice when they are using access runtime? my colleague is pretty sure this was possible at some point in older versions of access.

Answer 1

Hi. There are a couple of ways to do that. You can either create a database object based on the new file or directly query its MSysObjects table.

Answer 2

One does not exclude the other.

Regardless, to allow the user to select a file to import, simply use the FileDialog, refer to https://www.devhut.net/2016/10/04/late-binding-the-filedialog/

Answer 3

Excellent! thank you for pointing me in the right direction.

Modified my code to the below and it works great as I expected

Private Sub ImportTables_Click()
On Error Resume Next
DoCmd.Echo False
Dim Msg, Style, response
Style = vbYesNo
response = MsgBox("The existing tables will be replaced" + vbCrLf + "Are you sure you want to continue?", Style, "WARNING!!!")

   If response = vbYes Then  

Call DeleteTablesAll  
  
Dim sFile As String  
sFile = fFileDialog(msoFileDialogFilePicker, , , , "Microsoft Access,\*.accdb;\*.mdb;\*.adp;\*.mda;\*.accda;\*.mde;\*.accde;\*.ade")  
  
Dim db As DAO.Database  
Dim tdefs As TableDefs, tdef As TableDef  
Set db = DBEngine.OpenDatabase(sFile)  
For Each tdef In db.TableDefs  
    If Left(tdef.NAME, 4) <> "MSys" And Left(tdef.NAME, 4) <> "USys" Then  
    DoCmd.TransferDatabase acImport, "Microsoft Access", sFile, acTable, tdef.NAME, tdef.NAME  
    End If  
Next tdef  
  
Else  
DoCmd.Echo True  
    Exit Sub  
End If  
MsgBox ("Done!")  

DoCmd.Echo True
End Sub

Answer 4

Why not simply run queries directly against the old db tables to import into the new one?

Answer 5

Hi Yodite,

Runtime mode is an Access operating mode in which certain Access features are not available by default. Some of these unavailable features can be made available in runtime mode, however some features are not available at all:

• Special keys
• Navigation Pane
• The Ribbon
• Design view and Layout view
• Help

For detailed information, see this article.

Besides, please check the following link for reference:

Can you import data into an Access database using only the runtime environment?

***Third-party link disclaimer:***Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Regards,

Neha