Why am I not getting all records to import from an Excel file into an Access 2003 table using the DoCmd.TransferSpreadsheet command?
I am developing an Access 2003 mdb type application and I don't understand why I am not importing all records into an Access internal table from a Microsoft Excel 2003 application.
I have attached the Access 2003 mdb file along with one of the Excel files I am trying to import.
From my Interface I browse for an Excel file and then click on the Import command button.
The table imports the Excel file records from the worksheet titled Book Debits (C) correctly but does NOT import the Excel file records from worksheet Book Credits (D).
Do you know how I can resolve this issue?
Copy-of-101-07-24-2013.xls
I have attached the Access 2003 mdb file along with one of the Excel files I am trying to import.
From my Interface I browse for an Excel file and then click on the Import command button.
The table imports the Excel file records from the worksheet titled Book Debits (C) correctly but does NOT import the Excel file records from worksheet Book Credits (D).
Do you know how I can resolve this issue?
Copy-of-101-07-24-2013.xls
Seems to import Book Credits (D) just fine...
Or am I missing something?
Or am I missing something?
ASKER
Did you run my Access application and click on the Import command button from my Interface.
Then inspect the OpenItems table to see if both records were populated into the Access table from the Excel file. I can only get the detail record from Book Debits (C) to populate into the Open Items table. I cannot get the detail record from Book Credits (D) to populate into the table. I don't know why.
Then inspect the OpenItems table to see if both records were populated into the Access table from the Excel file. I can only get the detail record from Book Debits (C) to populate into the Open Items table. I cannot get the detail record from Book Credits (D) to populate into the table. I don't know why.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just curious. What did you find out?
ASKER
In my import, I start out by importing to a temporary table [Bank Credits (A)].
Then I execute the following SQL statements to insert the records from [Bank Credits (A)]
into a more permanent table named tblOpenItems:
As you can see in the SQL code, I use the cdate function on columns f2 and f1.
In the Excel file I import the records from, the user put leading apostrophes in the
date fields f1 and f2. So the dates were imported as text due to the leading apostrophes.
And this caused an error during the conversion.
ssql = "insert into tblOpenItems (bank, office, checknum,refno, [trans date], type,[process date], agedays, t, description, amount, manual,rptid, [report name], footnote,responsibility) "
ssql1 = " SELECT '" & bankNum & "',f7,f8,f10, cdate(f2),f4,cdate(f1), GetNumberOfWorkDays(f1, now()), f3,f5,f9, 'I',( select rptid from tblbanks where [bank code]='" & bankNum & "'),( select [report name] from tblbanks where [bank code]='" & bankNum & "'), f11,f14 FROM [Bank Credits (A)] WHERE (IsNumeric([f9])<>False) AND (F1 Is Not Null);"
rst.Open ssql & ssql1, con, 2, 2
Then I execute the following SQL statements to insert the records from [Bank Credits (A)]
into a more permanent table named tblOpenItems:
As you can see in the SQL code, I use the cdate function on columns f2 and f1.
In the Excel file I import the records from, the user put leading apostrophes in the
date fields f1 and f2. So the dates were imported as text due to the leading apostrophes.
And this caused an error during the conversion.
ssql = "insert into tblOpenItems (bank, office, checknum,refno, [trans date], type,[process date], agedays, t, description, amount, manual,rptid, [report name], footnote,responsibility) "
ssql1 = " SELECT '" & bankNum & "',f7,f8,f10, cdate(f2),f4,cdate(f1), GetNumberOfWorkDays(f1, now()), f3,f5,f9, 'I',( select rptid from tblbanks where [bank code]='" & bankNum & "'),( select [report name] from tblbanks where [bank code]='" & bankNum & "'), f11,f14 FROM [Bank Credits (A)] WHERE (IsNumeric([f9])<>False) AND (F1 Is Not Null);"
rst.Open ssql & ssql1, con, 2, 2
I'm glad you were able to figure it out.
ASKER
RDB1.mdb