Page 1 of 1
I just inherited a report generating ACCESS database
Posted: Tue May 30, 2006 8:16 am
by Tikker
and I know fuck all about access really
the dude has a query of a seperate db dumping shit into excel files, which are then brought into the access DB via the import external data function
i think it's lame because according to his documentation, you have to import a xls for every single day
is there a way to batch job it?
Posted: Tue May 30, 2006 8:23 am
by Zanchief
Why is he exporting it into Excel?
Just cut excel out of that shit and import it directly.
Posted: Tue May 30, 2006 8:36 am
by Tikker
Zanchief wrote:Why is he exporting it into Excel?
Just cut excel out of that shit and import it directly.
I'm not sure atm to be honest
He's using an db query tool (Brio Intelligence) to grab stuff daily from an alarm collection database, and dumps that to excel
I think the limitation is that Brio won't talk to Access
Posted: Tue May 30, 2006 8:55 am
by Gidan
Do you happen to have the time and experience to rewrite it? Why type of DB is it pulling the data out of? With C# or VB you could probably easily pull the data from the original DB and update access directly. This could automate the process and take excel out the mix completely.
Posted: Tue May 30, 2006 8:58 am
by Naethyn
is it using odbc
Posted: Tue May 30, 2006 9:12 am
by Tikker
gidan wrote:Do you happen to have the time and experience to rewrite it? Why type of DB is it pulling the data out of? With C# or VB you could probably easily pull the data from the original DB and update access directly. This could automate the process and take excel out the mix completely.
2 of the databases are Oracle, the 3rd is Sybase
pert of the issue is that I'm restriced to the "Standard Operating Environment" tools that the IT department has allocated our group(brio, excell, access) as well as partly constrained by the job description~
maybe an easy fix would be just to concatenate all the dailies into just 1 big .csv
Re: I just inherited a report generating ACCESS database
Posted: Tue May 30, 2006 9:15 am
by Lyion
Tikker wrote:and I know fuck all about access really
the dude has a query of a seperate db dumping shit into excel files, which are then brought into the access DB via the import external data function
i think it's lame because according to his documentation, you have to import a xls for every single day
is there a way to batch job it?
The reason he's probably going to Excel is it's cleaner for the Access import.
I'm of the opinion if it isn't broke, don't fix it. Depending on how you want to schedule it, it shouldnt be too tough to automate it, even without changing a thing.
Posted: Tue May 30, 2006 10:27 am
by Tikker
well, that's what I'm asking
how do I automate the import of the excel worksheets into Access?
Posted: Tue May 30, 2006 10:41 am
by Lyion
The easiest way is just to use VBA for your imports like
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import Spec", "c:\File name", True
Naethyn seems to be a Windows dude. See if he can help