asked on

Appending to Access via VBA

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"MSysTEMP", "C:\Users\ads\Desktop\" & iName

Hi Experts,

I have the above code to append an excel file to database.

Problem is, this is something I used in prior database and basic.

In the current database, my first field is called 'IDNUMBER'

When appending manually, I have to

- Change the type of this IDNUMBER field to 'text' (from double)
- Assign IDNUMBER as primary key.

Rest remains the same.

How do I incorporate the above 2 in the append query above?

Thanks for the help!

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

Hi,

I am already appending to a temp table (although in above code I have different name - it will be called table2)

This temp table will get appended to another table called 'Table1'

Question is, when I am appending to table1, I am using this query

INSERT INTO table1
SELECT table2.*
FROM table2
WHERE table2.IDNUMBER Not In (select IDNUMBER from [table1]);

How do I incorporate making the ID number a text and a primary field (prior to appending)?

Thanks!

Rey Obrero (Capricorn1)

* you don't need to make the ID number field as a primary key before appending.

INSERT INTO table1(IDNumber,field2,field3)
SELECT cstr(IDnumber), field2,field3
FROM table2
WHERE cstr(table2.IDNUMBER) Not In (select IDNUMBER from [table1]);

how many fields are involved here?

Shanan212

ASKER

60 fields are involved. However, on the excel-side I created a unique ID (which is of course is the IDNUMBER) and the duplicates are removed using unique ID only.

This worked with the sample set of data using the query I posted above.

Do I now have to include all fields as you noted? if then how?

[table1][field1], [table1][field2]

^ like that?

Or

INSERT INTO table1(IDNumber,SubNo,Address, Origin)
SELECT cstr(IDnumber), SubNo,Address, Origin)

^ as above?

Thanks for the fast help!

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

Please see the attached.

Its giving me error.

Each table has 10 records. Table2 (the to-be-appended) has one unique record out of the 10.

When trying to append, I get msg saying 'Acces is about to append 110 records'

Then another message saying 'MS Access cannot append all the records...MS Access set 0 fields to null due to type conversion failure and it didnt add 110 records to due to key violations'

Any help?

This error never came up in previous query I ran though.

Thanks!

Although this question went into appending a table with another, my original question was to append a table TO access.

Just branching off from our above topic, when I am appending the excel file to Access to be appended to another table (for the cstr conversion) I am getting import errors on the field 'IDNUMBER' since my first appending converted the text as double.

If I can get this 'first appending' via VBA, then rest should be fine using my query above - which is my goal as I want to automate it
Temp.PNG

Rey Obrero (Capricorn1)

upload a sample db with the two tables

ASKER CERTIFIED SOLUTION

Shanan212

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

Got it!