import append query

[splreece]

Morning all,

I am new to import via vba/access so its probably a basic question.

I have a current dbase, that could be updated daily if needed.

The dbase is small (only 7 fields), but the entries are in the 1000s.


I am looking for a way to update the current dbase by:

- If entries in the import file and dbase have the same Unique identifier (txtAppNo), then the entry is left as is except one field is updated with the new imports string (numCount).

- If import file has entry that dbase doesn't (therefore a new entry), then it imports a new entry to the dbase.

- if dbase has entry that import file doesn't, then do nothing.


Any thoughts or any good resources to start this process.

I have heard it is actually relatively easy and simple, but for a newbee I am struggling with where to start.

 

[sneuberg]

What's the format of the file to be imported, e.g., Excel, CVS?

 

[splreece]

What's the format of the file to be imported, e.g., Excel, CVS?

Thanks for the quick reply.

Its CSV files athat re used for daily datadumps.

 

[sneuberg]

Ok since it's not a spreadsheet as your first step I suggest linking to the CSV file. In the External Date tab, Import and Link section, click on Text File. On the Get External Data dialog browse to the file to link to, select Link to the data source by creating a linked table and click Ok then go through the rest of the steps to create the link table.

For the next step you can create an unmatched query and convert that into an append query or if you make txtAppNo a unique field in the existing table. You can append to it from the linked table and duplicate records just won't be inserted. You can use DoCmd.SetWarnings = False in your code to suppress the warnings about duplicate records.

The final step would be to run an update query. You will be updating records that don't need updating as they were just imported but so what.

This is a rather terse instruction but give it shot and get back to us if you get stuck.

 

[splreece]

Thanks for the quick response..

I've created a new simple dbase to test it.


tblMasterTable = the original (main table)
ltbl_CSVImport = the linked import file
qry_Append = the append query


The Initial Query works great.

But on append I get the error message

access cant append these records in the append query...
"conversion failure, key violations, lock violations"

Ok since it's not a spreadsheet as your first step I suggest linking to the CSV file. In the External Date tab, Import and Link section, click on Text File. On the Get External Data dialog browse to the file to link to, select Link to the data source by creating a linked table and click Ok then go through the rest of the steps to create the link table.

For the next step you can create an unmatched query and convert that into an append query or if you make txtAppNo a unique field in the existing table. You can append to it from the linked table and duplicate records just won't be inserted. You can use DoCmd.SetWarnings = False in your code to suppress the warnings about duplicate records.

The final step would be to run an update query. You will be updating records that don't need updating as they were just imported but so what.

This is a rather terse instruction but give it shot and get back to us if you get stuck.

 

[splreece]

ahh ignore that.. I have it working however its saying duplicate output decision when I am trying to import the AppNo field as well (works fine when I don't import appno)

 

[sneuberg]

But on append I get the error message

access cant append these records in the append query...
"conversion failure, key violations, lock violations"

Yeah, that's the idea. It won't append duplicate records but if you run the query anyway it appends the one that are not duplicates. If you use this method then in the button code for doing this you will have something like:

DoCmd.SetWarnings False
DoCmd.OpenQuery "TheQueryName"
DoCmd.SetWarnings True