Uploading Files to Access DB

[Sherianmartin]

I am trying to upload a file to Access DB, however the destination file has more columns than the file being uploaded. Not sure how to accomplish this.
[z{email redacted per site policy}]

[zmbd]

Sherianmartin:
A little more detail would be helpful:
Access Version;
Source file format;
What have you tried so far;
etc?

Also please see our FAQ on asking questions. As a general rule, all questions should be answered here in the public forums and not via PM or third-party emails.

[Sherianmartin]

I am using Access 2010. The file I'm uploading is Excel 2010. I am trying to overwrite or append an existing file in access with the new excel file. However, the access file has a lot more columns than the excel file. I don't want to recreate the access file. I might be able to use an append query, but I'm not sure how that works. Thanks.

[twinnyfo]

Sherianmartin,

You can link the Excel file to your DB (External Data, then Excel), and then the DB will treat the Excel worksheet as a Table.

To build an append query, go to Create, Query Design, select the linked Spreadsheet, then under Query Tools, select Append Query. Choose which field in the Excel Spreadsheet corresponds to the field in the Table.

Let us know if you need additional assistance.

[Sherianmartin]

What I really want is one table with all the fields. The Access file has all the fields, however the excel file doesn't, however it has the current information that I need. I will receive an excel file every month and I would like this process to be automated as much as possible. thanks.

[zmbd]

Easy enough, copy your XL.File and name it, "importme.xlxs" (the name doesn't matter). Now do as TwinnyFo has said, link to the file, create an append query and run it.

Next month, receive your file, save over your old XL.File as importme.xlxs in the same location, and run your append query.

There are ways to automate the copy/renaming of the XL.file and running the append query via VBA script; however, that would require a new thread. (^_^)

[Sherianmartin]

Does anyone know why date fields come over as text when they are uploaded from Excel to Access? Just had this happen to a file I'm working with. Any suggestions?

[Sherianmartin]

Hi, I am trying to change the datatype in Access from Text to Date/Time. However, I keep getting an error message "Not enough disk space or memory". Not sure why or how I can fix this. Thanks.

[twinnyfo]

Concerning Post #7, the values in your Spreadsheet are probably saved as Text, rather than as Dates. You can perform a conversion at runtime, such as:

CDate([DateField])

As long as the date field "looks" like a date, it should convert it into an actual date.

Not sure why you would be getting the error in Post #8.....

[zmbd]

--- It appears that the original question has been answered

Briefly, because this might happen to others that are also importing data, on cause to Post#8's memory issue is an annoyance in ACC2010 that was introduced in ACC2000. In earlier versions, at least it didn't cause the memory error.

Failures caused by Name AutoCorrect - Allen Browne

Close your database and re-open is the usual short term fix. I will follow the above link's advice on every new database I create due to the issues.

The other possible root cause is that the PC physically doesn't have enough RAM to handle the data conversion.