Solved INSERT INTO works only on local table (1 Viewer)

adygelber

New member
Local time
Today, 12:04
Joined
Mar 3, 2015
Messages
4
Hi guys!

I am facing a really weird problem.
I am using this code:
SQL:
.RunSQL "INSERT INTO TBL_ALL_EMPL_DATA (" & tableFields & ") " & _
        "SELECT " & inputReportFields & " " & _
        "FROM [Excel 12.0 Xml;HDR=YES;DATABASE=" & inputReportPath & "].[WorksheetName$]"
in order to import some columns from an excel worksheet to a table from my Access DB.

The problem is that the code works perfectly if the table TBL_ALL_EMPL_DATA is local but if I link it from the backend it doesn't work anymore throwing the following error:

error.jpg


To give you a more clear overview, in the past I was using something like this:

SQL:
.RunSQL "INSERT INTO TBL_ALL_EMPL_DATA (" & tableFields & ") " & _
        "SELECT " & inputReportFields & " " & _
        "FROM [Excel 12.0 Xml;HDR=Yes;Database=" & inputReportPath & "].[WorkheetName$] MR " & _
        "INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=" & inputReport2Path & "].[WorksheetName2$] OS " & _
        "ON OS.[ColumnA] = MR.[ColumnA]"

in order to do the same thing but joining 2 excel files. This version works even with linked table but now I need to remove from the query the second excel file and keep only the first one.

Do you have any clue why, if I am removing from the query the INENR JOIN clause, the query doesn't work anymore on linked table?

Thanks a lot for your support!
Cheers,
Adrian
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,357
Hi Adrian. My guess is there's a fundamental difference in the structures between your local and linked tables. You could try deleting the local table and then importing the linked table as a local table just to see if the SQL code would still work using the new local table.
 
Last edited:

adygelber

New member
Local time
Today, 12:04
Joined
Mar 3, 2015
Messages
4
Hi DBguy!

I tryed this and, on the same structure, on local works and on linked no.

I managed to understand why the error occurs, there are fields in Excel which are recognized as number and my table field in access is Short Text type. I have put an TextToColumns in excel and I tranform all the data in text before importing it and now it works.

I somehow believe that on linked tables there are some more checks in place, they are more pretentious :)

Thanks you very much!
Cheers,
Adrian
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:04
Joined
Sep 12, 2006
Messages
15,613
Hi DBguy!

I tryed this and, on the same structure, on local works and on linked no.

I managed to understand why the error occurs, there are fields in Excel which are recognized as number and my table field in access is Short Text type. I have put an TextToColumns in excel and I tranform all the data in text before importing it and now it works.

I somehow believe that on linked tables there are some more checks in place, they are more pretentious :)

Thanks you very much!
Cheers,
Adrian

A linked table is not the same as an Excel sheet. A table will have defined columns. Access will "guess" the column types on an excel sheet from the first few rows, and some of the data may be rejected later as not acceptable, or cause type mismatch errors

Another way of dealing with a spreadsheet is to import it to an access table, and then run validation checks to make sure the data is acceptable.

You have to be very careful with spreadsheets. A user may try to import completely the wrong spreadsheet. I add lots of validation steps when importing data from a csv, excel sheet, or any external text file.
 

adygelber

New member
Local time
Today, 12:04
Joined
Mar 3, 2015
Messages
4
A linked table is not the same as an Excel sheet. A table will have defined columns. Access will "guess" the column types on an excel sheet from the first few rows, and some of the data may be rejected later as not acceptable, or cause type mismatch errors

Another way of dealing with a spreadsheet is to import it to an access table, and then run validation checks to make sure the data is acceptable.

You have to be very careful with spreadsheets. A user may try to import completely the wrong spreadsheet. I add lots of validation steps when importing data from a csv, excel sheet, or any external text file.
Thanks for sharing your experience!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,357
Hi DBguy!

I tryed this and, on the same structure, on local works and on linked no.

I managed to understand why the error occurs, there are fields in Excel which are recognized as number and my table field in access is Short Text type. I have put an TextToColumns in excel and I tranform all the data in text before importing it and now it works.

I somehow believe that on linked tables there are some more checks in place, they are more pretentious :)

Thanks you very much!
Cheers,
Adrian
Hi Adrian. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom