I just inherited a report generating ACCESS database

[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?

[Zanchief]

Why is he exporting it into Excel?

Just cut excel out of that shit and import it directly.

[Tikker]

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

[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.

[Naethyn]

is it using odbc

[Tikker]

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

[Lyion]

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.

[Tikker]

well, that's what I'm asking


how do I automate the import of the excel worksheets into Access?

[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