Importing - Key Field Specification

[DBFIN]

I'm importing a csv file into a table via a transfer text macro that is based on specifications requiring the Account No field to be indexed. The macro correctly imports the file, but the Account No field in the table is not indexed. It appears the specification that requires the indexing of the Account No field is lost.

How do I fix this ?

 

[boblarson]

Have you tried setting up a table with the correct structure and then importing to that? I'm assuming, from your original post, that you are basically making the table over each time.

 

[DBFIN]

I'm trying to overwrite the table each time the macro is run, so setting the table with the correct structure is lost when the table is overwritten. I tried linking, but the linking function does not allow the option to index a field.

 

[boblarson]

You shouldn't overwrite the table each time. Just import the data. Then you don't have a problem. If you can't get it to import properly then import to a temp table name that will always be the same and use an append query to place it into the real table where you have set up the correct data info.

 

[DBFIN]

I've actually done what you suggested in the past. The file contains 1.2 million records with 36 fields. The append can't be done in one step due to data overflow, but can be done in multiple appends which is very time-consuming.

I found the import can be done in one step without data overflow, yet I can't get past the index problem. The table is overwritten because the transfer text function will append instead of overwrite if I don't delete the table each time.

 

[boblarson]

Instead of deleting the table, have you thought of just using a delete query to clear the table first?

"Delete * FROM myTableName"

 

[DBFIN]

I'm a sophisticated user of access, but not a programmer. I don't know how to build a delete query. Where do I put the code: "Delete * FROM myTableName" ? In the criteria of the delete query ?

 

[boblarson]

No coding required for it, just go to the database window, click NEW for new query and then select DESIGN VIEW.

Next double-click on your table to add it to the query window and then click the close button.

After that, go up to the menu bar and select DELETE QUERY from the list of available types.

Then, in the query window, there should be a list of fields with an asterisk above them all. Double click on it to add the asterisk to the selected fields.

Save the query like del_qry_DeleteMyTableData and then all you have to do is run it when you want to clear the records. Docmd.OpenQuery should do it for you.

 

[DBFIN]

I completed the delete query, which will probably be the ultimate solution to the index problem. I just need to test a little further, but it looks like you solved my problem. Thank you very kindly sir.

 

[boblarson]

 

[DBFIN]

One final point for clarification. I deleted Table A of all records then imported the csv file into Table A using no specifications in the macro's transfer text action. I'm assuming the table will always maintain the same field properties that were in the original Table A setup even if the data format of the imported csv file differs from the Table A. Correct ?

 

[boblarson]

That is what should happen, yes.