I thought I had this figured out but... nope.
I have a small database that is split and used by multiple users for reports only. No input into forms.
The only input is from a single Excel file that is generated from another online program.
I want a way to import the the new Excel file into the Access db and be able to replace it when a new Excel file is generated.
What is the best way to set this up?
My thought was to create a link to an Excel csv file or xlsx, using the Import Excel or Import Text File.
The save this import specification and run it via VBA through the click of a but on the main form.
The problem with the Excel csv file is that some of the columns have commas integrated in the cell. This throws everything off and the data is useless.
The problem with the Excel xlsx file is that Access will select the Number format of some fields that actually have alphanumeric when it imports them.
The problem with the Text File import is that I was not able to delete the records in the text file when I import the new file.
What are some good ways of setting this up?
I have a small database that is split and used by multiple users for reports only. No input into forms.
The only input is from a single Excel file that is generated from another online program.
I want a way to import the the new Excel file into the Access db and be able to replace it when a new Excel file is generated.
What is the best way to set this up?
My thought was to create a link to an Excel csv file or xlsx, using the Import Excel or Import Text File.
The save this import specification and run it via VBA through the click of a but on the main form.
The problem with the Excel csv file is that some of the columns have commas integrated in the cell. This throws everything off and the data is useless.
The problem with the Excel xlsx file is that Access will select the Number format of some fields that actually have alphanumeric when it imports them.
The problem with the Text File import is that I was not able to delete the records in the text file when I import the new file.
What are some good ways of setting this up?