Calling Append Queries during Import 1

[Quintios]

I have existing information in Excel that I'd like to import into Access. During database design it was determined that the flat-file should be setup as a main table and a couple subset tables. There are five columns in Excel that are used for salary or hourly information. They weren't split into separate columns, they were just left in the same five columns to prevent folks from having to scroll all over the place; when you're looking at the spreadsheet, you can tell who is what. It ain't pretty, but it is efficient.

Anyway, is this possible to do automatically? Can I start an import into Access and then start splitting out the information into different tables? Shoot, I'd be happy if I could do it manually, putting the information into one big import table and then performing append queries into the other tables. What I'm not sure how to do is a conditional append query. If a record is indicated as being a salaried employee, then import into tableX; if the next employee is not, then don't import, etc. How would I ensure that the primary key, which happens to be Autonumber and not EmployeeID, is the same between the main table and the subset table?

So, with a macro or VBA, can I start running append queries?

Lots of questions, whatever you can answer will help!

Thanks in advance!

Onwards,

Q-

 

[B827]

I have done something similar, however it is a bit complex and I have to go now. If no one has posted anything by tomorrow I'll put some information in another posting. Sandy

 

[wbuckles]

It is complex. I've done similar things with macros. Basically, I get a flat file each day on a shared drive. I import it, and then run an append to different tables, and then a delete. The delete removes info from the original table if it matches a record in the new table.
Here's a microsoft knowledge base article on deleting duplicate records between tables:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q98664

You can run a scheduled event to run this automatically every day, or run it manually. Or put a macro shortcut on your desktop that you can click to run it when you receive the Excel file.

 

[B827]

This is waht I do in similar circumstances. Assuming your Excel spreadsheet is always named the same and in the same location then this process should work. This is in VB, I prefer using VB for this sort of thing because you can turn the warnings back on if the process errors - very important. The reason you turn off the Warnings is so you don't get messges about adding and deleting records.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClearTable1", acViewNormal, acEdit
DoCmd.OpenQuery "qryCleartable2", acViewNormal, acEdit
DoCmd.OpenQuery "qryCleartable3", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Spreadsheet_details", "Filename", True
DoCmd.OpenQuery "qryAddToTable1", acViewNormal, acEdit
DoCmd.OpenQuery "qryAddToTable2", acViewNormal, acEdit
DoCmd.OpenQuery "qryAddToTable3", acViewNormal, acEdit
DoCmd.SetWarnings True

If you are using Excel97 and Access97 then if you have a large number of records in the spreadsheet (more than 32K rows) then you have to have the spreadsheet open whilst importing or the records beyond row 32255 (or thereabouts) don't get imported. It is best to set up the table you initially import to manually by importing some example spreadsheets. Once it is all set up then it works very reliably. If you want to to this automatically then the simplest way is to set an autoexec macro to run the code then close the database and access, and schedule an event to open the database. We have a few systems that do this without any problems Sandy

 

[Quintios]

OK, now we're getting somewhere!

Now here's the trick, is there a way to prevent Access from creating an error table? A fellow around here produced some code that would export the errors to a .csv file. The file would tell you where the error was so you could go back and fix the entry that had the problem.

Also (and I'm searching for this right now), what is the syntax for using an import spec when using the transferspreadsheet function?

Lastly, are you familiar with the 'Connect' property of TableDefs? Whenever I go into 'help' for importing data I eventually run across this statement. I don't know anything about DAO (or ADO for that matter) but if it's a better method I'd rather use it. What do you think?

Thanks very much!

Onwards,

Q-

 

[Quintios]

Found the syntax, just use the TransferText function.

Onwards,

Q-

 

[B827]

I assume that you mean the import errors table? I prefer to put a lot of effort into getting the destination table specification correct. For example setting to text if there is any chance of a non numeric character, setting allow zero length on for fields where nulls could be expected and so on. This is worth the effort.

I beleive that specifications are only available for the transfertext action. Sandy