Automate Export of Data to an Access Table

[KniteMare]

I put together a little table for one of my LAN based facilities to track their Overtime Savings as compared to each of their individual units. It works great.

What I would like to do is to export the data on a weekly basis to Access. I set up an access table to recieve the data and I can get it all to work just fine except I want the users to just click on a menu item and have the export take place in the background.

I know how to create on the fly menus and get them to accecpt commands from Excel Macros so that is not an issue.

I know how to share data in Excel from Workbook to workbook, closed or open. I'm sure it can be done Excel to Access I just can not for the life of me find anything to jumpstart me in the right direction in either application's help files. Anybody out there linking their Excel data to Access?

Yours in EXCELent Frustration
KniteMare

 

[NateO]

Hello,

Same question... At what point in your code do you add a new Record and actually add the data? E.g.,

http://www.mrexcel.com/board2/viewtopic.php?p=1232569#1232569

:wink:

 

[Barkeep]

Thanks for looking at what little I had, Nate.

After pondering over how to go about my task, I ended up taking a much simpler route.

I had Excel to run an Access TransferSpreadsheet macro; which brought in the data I needed.

In Excel:

    Dim A As Object
    Set A = CreateObject("Access.Application")
    
    A.Visible = False
    A.OpenCurrentDatabase ("M:\DRD\Callables\Callables.mdb")
    A.DoCmd.RunMacro "Clear and Close DB"

-Mike

 

[NateO]

You are welcome.

On an Open Workbook? When you close Excel is the Excel.exe process terminated in your task manager (which version of Office are you using)?