Append Access Database with Excel Table, While Excel is open and Access closed... (1 Viewer)

mstein23

New member
Local time
Yesterday, 19:54
Joined
Oct 28, 2024
Messages
1
I have an Excel Sheet that holds a Table (Table named "PST_2" ) that I need to append to an Access Database (DB named "STATS.accdb"). I need to do this while the user has Excel open, and without opening the Access application, as some users may not have it. I know how to initiate appending from Excel to Access while using Access. But I'm having trouble finding clear info online of how to initiate the opposite direction with a macro/VBA from within Excel, without the user having to open Access.

Here are the filenames and paths:

Excel File Name: TDCD_GAME.xlsm
Excel File Path: C:\\TDCB\TDCB_GAME.xlsm
Excel Sheet: PSTLD
Excel Table: Named PST_2, actual range on PSTLD sheet will vary as it's dynamic
Access File Name: Stats.accdb
Access File Path: C:\\TDCB\Stats\stats.accdb
Access DB Table to Append: PST

* All headers in both the Excel Table ("PSTLD") and Access Table ("PST") match exactly *

Any input would be greatly appreciated!
 
, as some users may not have it.
maybe not possible for "some" users.
you need access connectivity to open ms access database.
and ms access database will be Open to be able to append your data.
 
A quick net search suggests that this isn't usually done because if the DB is Access-based, it is usually maintained using Access. Most of the time, the update is pointing in the other direction. However, you might be able to download a Microsoft Database Engine redistributable program and try to open the redistributable as an application object.

If you CAN do this, it would probably be started by opening a DAO or ADO recordset pointed to the particular database file. Like arnelgp, I have some misgivings about doing so in the absence of the Access run-time environment. But I'm not hard-headed enough to say it is impossible.
 
I need to do this while the user has Excel open, and without opening the Access application, as some users may not have it. I
If you want to update a database, you need software that is capable of doing that. For .mdb's, the software is Jet. For .accdb's the software is ACE. Jet can be installed without MS Access but I don't believe that ACE can be installed without MS Access. See if you can find a download for the ACE database engine. I think at a minimum you will need to install the Access runtime engine on the PC for any user who needs to update the Access database.
 
Why not just link to the Excel file in Access?, then the data will be available in Access?
 
If the user does not have access but is able to see the BE then you would probably need to use the ms ADO library (6.1?) and create an append query or more likely step to the end of a created recordset and addnew/update.

You can return a recordset without any records by using a criteria 'WHERE False' or 'ID<0' (depending on the nature of your fields)

see this link for connection strings

 

Users who are viewing this thread

Back
Top Bottom