Avatar of Shanan212
Shanan212
Flag for Canada 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!
Microsoft Access

Avatar of undefined
Last Comment
Shanan212

8/22/2022 - Mon
SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shanan212

ASKER
Got it!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.