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

WiWo

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
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

AWF VIP
Local time
Today, 05:04
Joined
Jan 11, 2013
Messages
3,913
I believe @Steve R. works with MariaDB. He'll get a notification of a mention so hopefully he will see your post.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:04
Joined
Aug 30, 2003
Messages
36,125
Hopefully Steve replies, I've never worked with MariaDB. My first thought was TransferDatabase to import the tables:


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

Steve R.

Retired
Local time
Today, 05:04
Joined
Jul 5, 2006
Messages
4,682
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
 
Last edited:

Shimon

New member
Local time
Today, 12:04
Joined
Dec 17, 2023
Messages
7
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

Well-known member
Local time
Today, 11:04
Joined
Feb 7, 2020
Messages
1,942
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

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
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

Well-known member
Local time
Today, 11:04
Joined
Feb 7, 2020
Messages
1,942
Code:
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

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:04
Joined
May 7, 2009
Messages
19,230
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

Well-known member
Local time
Today, 11:04
Joined
Feb 7, 2020
Messages
1,942
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

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:04
Joined
May 7, 2009
Messages
19,230
add a Loop that will try to Open the tabledef successfully, with DoEvents in between.
 

WiWo

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
sounds good but can you give a sample code for this?
 

ebs17

Well-known member
Local time
Today, 11:04
Joined
Feb 7, 2020
Messages
1,942
Code:
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.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:04
Joined
May 7, 2009
Messages
19,230
not tested:
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:04
Joined
May 7, 2009
Messages
19,230
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

Well-known member
Local time
Today, 11:04
Joined
Feb 7, 2020
Messages
1,942
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

New member
Local time
Today, 11:04
Joined
Mar 20, 2024
Messages
7
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!!!
 

Users who are viewing this thread

Top Bottom