Connect / Import MariaDB table(s) into Access (1 Viewer)

[WiWo]

I want to have a FRESH LOCAL copy of the external DB table(s), every time I open te Access DB.

When I choose for a "connected" table, it is not a "local" copy and performance is terrible.
But when I choose to import the table, it is a "local" copy but then I have to do this manually every time.

The goal is to have a macro or VBA that can do this automatically on open. Any suggestions?

 

[moke123]

I believe @Steve R. works with MariaDB. He'll get a notification of a mention so hopefully he will see your post.

 

[pbaldy]

Hopefully Steve replies, I've never worked with MariaDB. My first thought was TransferDatabase to import the tables:

DoCmd.TransferDatabase method (Access)

Office VBA reference topic

learn.microsoft.com

Presuming that works, it would be relatively simple to drop/import the table(s) on start up.

 

[Steve R.]

I will have to look into. I don't have an immediate response.
@WiWo I do have some questions.

• Are you importing the data into MS Access as a one shot import?
• Or are you importing/exporting the data between MS Access and MariaDB on a continual basis?

It's been many years since I have worked with MS Access. Can MS Access import an SQL text file? Evidently MariaDB can export a total of 14 different file formats, of which SQL is one. Unfortunately "print screen" wouldn't show the full list from the drop-down box. So will have to see if I can find the list on a website or copy it by hand.

@pbaldy referenced the DoCmd.TransferDatabase method. Look for the words "ODBC Database". Theoretically, it should be possible to connect MS Access and MariaDB directly by installing an "ODBC connector". Again, it has been a long time since I last looked into it. So I will have to explore the issue.
------------------------------------------------------------------------------------------------------------------------------------------------------
PS: Quick finds. I have not fully explored the articles below. They may or may not help with resolving your question.
ODBC Connector
How can I read MYSQL (MariaDB) Data using MS Access
(MySQL and MariaDB are supposed to be interchangeable, however there are occasional exceptions.)
Update MariaDB Data with a Microsoft Access Linked Table
Connecting Microsoft Access to MySQL Using an ODBC Driver

 

[Steve R.]

The links below may be more inline with your original request to create a "local" database within MS Access.
Migrating data from MariaDB to MS Access(*.mdb; *.accdb)

How To Import and Export Databases in MySQL or MariaDB
The link above uses mysqldump . The question for you, as I identified in my prior post, can MS Access read an SQL text file for import into MS Access?

 

[Shimon]

Hi,
I would suggest having the MariaDB tables as linked tables, name them lnktTBL1, lnkTable2 etc. Create a duplicate set of tables names Table1, Table2 having the same structure.
Upon startup run a Delete query on Table1 and Table2 and then have an Insert (update?) query that copies all data from the lnkTable1 to Table1.
I have done this in the distant past for Foxpro tables. I didn't need to update them very often, so I had a form with various buttons to do these tasks.
Sincerely,
Shimon

 

[ebs17]

performance is terrible

Something like this is not God-given, but is most likely due to your specific access and queries.
The first way would be to eliminate all existing weaknesses.

A local copy of the tables only makes sense if the contents are only read. If new records are created or existing records are changed, you will fail with a subsequent attempt to synchronize. Given the slowness mentioned, larger amounts of data are certainly in use. You should not only copy content, but also pay attention to the necessary indexing; for functionality you should also use the same relationships as in the original.

 

[WiWo]

All folks, thank you for your suggestions.

First of all, I will try to explain what I have done already and what problems I encountered.

1) I have a (Joomla!) website and a web application that maintains a membership table in the Joomal! database
2) I have an Access DB that links, via an ODBC Connector, in read-only to this membership table.
3) I have queries, reports, forms that use this connected table.

So far, so good. Queries and reports run very smooth. But:
1) Once in a while, I get an error on the ODBC connection and then I have to close the Access DB and start over again.
2) When I open the table (only about 1000 rows; 30 fields) and type a search argument (e.g. membership ID or name) to filter for a specific row, it can taken over an hour to get an answer.

I tried, as an alternative, to import the table instead of connecting.
This works perfect. No connection problems and filtering works at lightspeed (more or less).
The only side effect is that the table is not refreshed automatically when I open the Access DB and explaining to a non-IT user how to do this??? Nightmare!

So, that is the reason of my question: is there a way to write a macro or VBA to achieve this on open (or by a click on a button).

The suggestion of Shimon could be a valuable alternative for the performance but as the connection stays alive, I fear that this would not solve the connection errors.

Willy

 

[ebs17]

Sub NewContent()
    Dim db As DAO.Database
    Dim sSQL As String

    Set db = CurrentDb
    sSQL = "DELETE FROM LocalTable"
    db.Execute sSQL, dbFailOnError
    sSQL = "INSERT INTO LocalTable SELECT * FROM LinkedTable"
    db.Execute sSQL, dbFailOnError
End Sub

 

[WiWo]

Hello Eberhard,

This module does the job but is there a way to execute the module at open? If not, how to connect a module to a button?

Willy

 

[arnelgp]

change the Sub into a Public Function, then create an AutoExec macro and call the function.

the only concern is it fails on the Second Execute, you'll get blank table?

 

[ebs17]

Opening the start form or clicking a button are some of the many possibilities of usable events. For such an event, you create an event procedure and call NewContent there.

 

[WiWo]

OK, everything works fine now. But the delete/copy takes some time to execute. How to "lock" the application until copy is finished? Or give a message at completion?

 

[arnelgp]

add a Loop that will try to Open the tabledef successfully, with DoEvents in between.

 

[WiWo]

sounds good but can you give a sample code for this?

 

[ebs17]

Sub NewContent()
    Dim db As DAO.Database
    Dim sSQL As String

    Set db = CurrentDb
    sSQL = "DELETE FROM LocalTable"
    db.Execute sSQL, dbFailOnError
    sSQL = "INSERT INTO LocalTable SELECT * FROM LinkedTable"
    db.Execute sSQL, dbFailOnError

    MsgBox "The recopying of the table is complete." & vbCrLF & vbCrLf & _
        db.RecordsAffected & " records have been copied."
End Sub

Now you could call up other useful actions, such as opening a specific form. You know yourself what you want to do.

But the delete/copy takes some time to execute

Instead of deleting everything and appending everything, you could forgo deleting and just add new records and adopt content changes.
For example, if you have 12,000 members whose data doesn't change and new members are constantly being added, which you might recognize because there is a timestamp for the record creation, then you would only use an append query to get exactly these new ones add members to the LocalTable.

 

[arnelgp]

not tested:

Sub NewContent()
    Dim db As DAO.Database
    Dim sSQL As String

    Dim rs As DAO.Recordset
    
    ' add status message
    SysCmd acSysCmdSetStatus, "Updating local table... please wait..."
    
    Set db = CurrentDb
    sSQL = "DELETE FROM LocalTable"
    db.Execute sSQL, dbFailOnError
    sSQL = "INSERT INTO LocalTable SELECT * FROM LinkedTable"
    db.Execute sSQL, dbFailOnError
    
    On Error Resume Next
    
    Set rs = db.OpenRecordset("LocalTable", dbDenyWrite + dbDenyRead)
    ' loop until there is no error
    While Err
        DoEvents
        Err.Clear
        Set rs = db.OpenRecordset("LocalTable", dbDenyWrite + dbDenyRead)
    Wend
    rs.Close
    
    Set rs = Nothing
    Set db = Nothing
    ' remove status
    SysCmd acSysCmdClearStatus
    DoCmd.beep
    DoCmd.beep
    DoCmd.beep
End Sub

 

[arnelgp]

then you would only use an append query to get exactly these new ones add members to the LocalTable.

consider also those been deleted on the backend.

 

[ebs17]

If it's necessary, sure.
The steps of deleting deleted records, updating changed records and appending new records can still be significantly faster than complete deletion + complete appending.

You just have to know how your own data can develop, and for high-performance query technology you would have to have made some preparations (indexes, timestamps, etc.).
Loops in recordsets would definitely not be my first choice.

 

[WiWo]

Thanks all of you!

As the table is not that big, time to delete/copy is acceptable and I implemented the simple solution Eberhard proposed.

Great help!!!