updating daily table

[knice11]

Hello.

I was asked to do a data project in access. I have no clue how to do this. I have never worked with access but have experience in the other ms office suites. The first thing I must do is setup a daily import process for a sheet in excel called PO status report. The sheet has 5560 records. How can I setup an automation process to import daily into the sheet. I heard macros was the way to go.

 

[JamesMcS]

So do you want to import from the spreadsheet to a database? There's a VBA function called transferspreadsheet that will achieve this, have a look in the help file and see how you get on.

 

[knice11]

I have seen that function. Will that do it daily though? Will it create a new data to put in a new spreadsheet daily?

 

[JamesMcS]

It'll do it as often as you call it. What exactly is it that you need to do? Export from Access to a spreadsheet, or the other way round?

 

[knice11]

Yes sir I need it export from access to a spreadsheet. I need it to do it automatically though, say like 2 am daily.

 

[JamesMcS]

OK. Nice and easy. Just use transferspreadsheet with the acExport option at the start, the help file will explain the rest.

But - you 'll need to have a PC running the database, and use the Timer event (in conjunction with the Timer Interval property) to check what the time is, say, every minute. Then, at 2am, kick off the transferspreadsheet line and robert's your father's brother

 

[knice11]

Just to make sure I'm clear, this done with a macros correct? There is a pc that will be running the database. Where is the timer event located? Access or excel? I'm just trying to make sure Im on the same page as you.

 

[JamesMcS]

I don't use macros in Access, but I believe it is a macro action as well as a VBA one.

The Timer event is located in the properties of a form in Access.