Replies

Thanks Hans,

I was trying to rebuild the zip with the queries changed and send them along, it really is only the first two anyway. I didn’t realize the question on how to selectively import records directly or delete and reimport others was so down in the weeds. I was just having trouble thinking it through.

Thanks anyway

In the on open event of a form I us ethe following:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
MaximizeRestoredForm Me

‘On Error GoTo ErrorHandler

If fIsFileDIR(“C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls”) = True Then

DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acImport, , “Pricing_Detail_EB_Export”, “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls”, True
DoCmd.TransferSpreadsheet acImport, , “Pricing_Export”, “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Export.xls”, True

DoCmd.OpenQuery “qryAppendToTarPricing”
DoCmd.OpenQuery “qryAppendToTARDetail”
DoCmd.OpenQuery “qryAppendToPricingProgramDetail”
DoCmd.OpenQuery “qryAppendToPricingProgramPricing”

DoCmd.OpenQuery “qryEmailTable”

DoCmd.OpenQuery “qryDeleteAfterImportDetail”
DoCmd.OpenQuery “qryDeleteAfterImportPricing”

DoCmd.SetWarnings True

DoCmd.SendObject acSendTable, “tblEmailChangeListTable”, “Text”, “#GRCT Code400”, , , “Pricing and Technical Report Data Added to TAR Template and Pricing Program”, “This email is auto forwarded and is sent to inform you that the TAR Template Database and the Pricing Program have been loaded/Updated with the latest Data from EB through ” & Date & “. If you have any problems please repond to mcneilkm@supship.navy.mil.”, False

Kill “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Export.xls”
Kill “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls”

Else

MsgBox “No Pricing / TAR Data to import at this time”, , “Pricing / TAR Import”

End If

End Sub

The queries in the attachment that I sent should point to the “Pricing” and “Pricing Detail EB” tables. I neglected to change the queries when I zipped.

The main idea here is taking the excel file, transferring the spreadsheet to an “Pricing_Detail_EB_Export” or “Pricing_Export” table, then using the queries to append that table to the “Pricing” or Pricing Detail EB” table. The other queries delete the data written to the “Pricing_Detail_EB_Export” or “Pricing_Export” tables after the append. Finally, I build a txt file and send as an attachment to an email listing the records imported.

Does this help any?

Sorry Hans,

In my haste I did not realize the linked tables would mess everything up. In any event, the attached should be able to be reviewed. Thanks for your help.

Thanks Hans,

Here is a zipped file.

Kevin

Before I do that (it contains company private info), let me try to explain the relationship. I have one table called “Pricing” which contains pricing related data at what I call the macro level. It has a unique pricing ID and specific data elements such as the name of rate schedules used to price it, specific contact info etc. The database has a related table called “Pricing Detail”. This table contain pricing data related to the “Pricing ” table through the unique ID. The data in this table is more along the lines of a line item detail of the “Pricing Record”. There are many line item records associated with the “Pricing” table record. The data from both of these tables comes from a contractor who will send us two excel files to import. Each file contains records for these tables. My problem happens when they send me a set of files containing records that are imported on one day, but may also send me on a seperate day in the future, “Pricing Detail” data for a specificf “Pricing that was already sent but updated for a current price. This updated set of records may contain more or less “Pricing Detail” records that were previously imported for an existing Pricing. (ex:On Monday i import a file with “Pricing” record which has a pricing ID of “123”. The coresponding “Pricing Detail” file has 15 records at the detail level with a pricing id of 123. On Wednesday I get another file that has new records with different pricing id’s as well as updated “Pricing Detail” records with a pricing id of “123”.) I need to be able to import the new records(previously never imported) as well as the updated records(ones that already exist, but may have different number of records with specific pricing id).

I hope this helps. I would have to delete all data in database in order to send it without the company private info.

Kevin

Thanks Hans,
I thought about that but here is what I thought was a problem to that solution. This import represents Pricing detail records from an outside source. These detail records relate to one record in another table. If the detail records already exist, it means that the outside source has updated their pricing information and it may have more detail records or less detail records as well as different data within the records. Maybe I am trying to think to hard through this process? If there are 10 records with a particular ID that already exists and the update query tries to handle 8 new records, what will it do with the 2 remaining records?

Kevin

Hans,

Just out of curiosity, When there is a Front End / Back End set up and all the tables in the BE are linked to the FE, does this impact any size limitations such as the 2 gig limit on the database? In other words, is the actual database size the combined size of the FE and BE, (because of the linked tables)?

thanks
Kevin

thank you !

Thanks Hans,

Sounds like this needs to be in a module with a call to that function from the log on form. Do i need to provide the “Full PathName”, or just the full name with the “.mdb” Extention?

Thanks
Kevin

Thank You it worked great

Yes, that is correct. It seems though that it is associated with one network printer. I have a total of three printers available, however only two of the three allow me to print from Access. I can print to all three with any other software.

Thanks,

I changed my default and it started working. Strange though, I can print to the other netwok printer just fine for everything else but ACCESS Reports.

No and I will, but just realized I can’t print any reports in any database.

thank you

Hans,

I have determined that the format with the multiple recipients works with one attachment. Does this method not support multiple attachments?

Thanks