Filling a new column with just one string value?

[DouglasMacArthur]

Hi all,

I'm new to this forum and relatively new to VBA, so from what I can tell, this problem should be pretty simple to resolve.

Basically, I need to read in a bunch of excel files to a single database table and add a column that contains the name of the file each entry originated from. My approach is to create a new table for every file, pass through the data, then create a column called "Source" and populate it with the name of the file. Then, I would pool all of those tables together into one.

So far, I have most of it done; I can pass through the data to individual tables and a column called "Source" is added to each one. Where I am stuck is filling in that column in each table with the file name.

My first approach was to just set the DefaultValue property as the file name, which executes and the Default Value is set, but this only fills one cell at the end with the file name. Now I've been trying to use SQL, but with no results.

Here's the line where I make my SQL attempt:

CurrentDb.Execute "UPDATE "" & TableName & "" SET [Source] = "" & sName & """

'Where TableName is a string variable referencing a specific table and sName is the file name

Perhaps I'm going about this ↑ entirely wrong but I can't really tell. As I said, I'm fairly new to this. Any help is appreciated!

 

[Isskint]

Hi and welcome

The answer is to have one table used for importing including the extra Source field (fields, not columns). Then your update query is essentially correct, replace TableName with the import table name. Include an APPEND SQL to add the import table data to the master table AND a DELETE SQL to wipe the import.
Are you copy/pasting the data, Using TransferSpreadsheet or custom code to read in your bunch of excel files?

 

[DouglasMacArthur]

(fields, not columns)

I know, I was just trying to be as clear as possible

Are you copy/pasting the data, Using TransferSpreadsheet

Yes, I'm using TransferSpreadsheet.

So basically, I use one table for the data I have and another for the source field and stitch them together, or one table overall? I originally was doing it the latter way, but I figured in order to do this in one table you would need to reference specific rows to fill the source field, rather than just filling the entire field in one table.

 

[Isskint]

Importing goes into 1 table - tblImport. Stored data goes in another table - tblMain.
tblImport contains all the fields in your source documents AND the field Source. When importing through TransferSpreadsheet an error will be generated as there is no [Source] field but you can work around that with error trapping.

I see you already capture the filename (or similar) in variable sName so your SQL statement to update [Source] in tblImport would be like

strSQL = "UPDATE [tblMain] SET ([Source] ='" & sName & "');"
DoCmd.RunSQL strSQL

As an aside i prefer to use DoCmd.RunSQL rather than Execute, unless you are using the error trapping or other methods exposed by Execute.

Another bit of SQL to add the amended tblImport to tblMain, then delete records in tblImport.
Have a look at the flow map.

 

[DouglasMacArthur]

Ah ok, I understand what you mean now, thanks for the thorough response!

EDIT: Most of this seems to be working ok, however my append approach doesn't seem to be working.

CurrentDb.Execute "INSERT INTO mainTable SELECT * FROM Table1"
    
CurrentDb.Execute "DELETE * From Table1"

The delete approach does work, but insert into does not. Anything you can see obviously wrong with it? It's not a compile error because I get no error messages, it just runs through everything but the main file stays empty