If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linked Tables - adding field and getting overflow error
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. |
#2
|
|||
|
|||
Linked Tables - adding field and getting overflow error
'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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "IrishRed" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|