Linked Tables - adding field and getting overflow error

[IrishRed]

Hello All,
Any suggestions would be greatly appreciated. This problem has been
bothering me for a week now.

I have a make table query which takes data from a linked Excel table and
creates a new 'Combine' table. When it creates the Combine table I also add
another field called 'Source' to the Combine table. I then have several
append queries which I link to additional Excel linked tables. I take this
data and add it to the Combine table along with a 'Source' field. I created
the source field because I needed to have a field which would tell me which
linked table the data came from for each row in my 'Combine' table.

I have two issues. One is that I have the concept of adding the additional
filed, however; it is not working the way I thought. I wanted the new field,
'Source', to be automatically populated with the table name that the data
came from but instead I am prompted to input the value for each query/table
being linked.

The second issue is that I am getting an overflow message when any of the
queries are run if my Select is in this format:
Select Table1.F16 as Source, *
but if I edit the SQL view and modify it to Select *, Table1.F16 as Source
then I can run it which in SQL view but if I try to save it then Access
changes it back to the first format which causes the overflow.

This is my make table query:
SELECT *, Table1.F16 AS Source INTO Combine
FROM Table1;

This is my append query:
INSERT INTO Combine
SELECT*, Table2.F16 AS Source
FROM Table2;

Thank you for your time.

 

[Allen Browne]

'Overflow' means there is a value that's too large to fit into a column,
e.g. a number larger than 32k for an integer column, or larger than 2.1
billion for a Long Integer.

The typical way to solve this is to create the table with the fields the way
you want it, and use the Append query to populate it.

Spreadsheets don't have typed columns. There are times when you could have a
nonumeric value in a spreadsheet column (e.g. "N/A"), and that won't import
into Access.

Perhaps you could append the data by creating a command button that executes
a query string:
Private Sub Command1_Click()
Dim strSql As String
strSql = "INSERT INTO Combine " & _
"(F1, F2, F3, ... TheSource) " & _
"SELECT [Table1].*, 'SomeName' AS Source " & _
"FROM [Table1];"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub