Appending to Access via VBA

Shanan212
Shanan212 used Ask the Experts™
on
   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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
you need to import the excel file in a temp table.

create an append query using the temp table with a column that converts the double to Text

select cstr(IDNUMBER)
from temp

Author

Commented:
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!
Top Expert 2016

Commented:
* 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?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
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!
Top Expert 2016
Commented:
it will be easier to create the append query using the query builder editor..

add all the fields from table2

then modify the IDNumber field like this

Field  Cstr(IDNUMBER)      Field2
table  
sort
append to   IDNUMBER     Field2

Author

Commented:
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
Top Expert 2016

Commented:
upload a sample db with the two tables
Hi,

I just came to say this - I did it with the following

- I made the IDNUMER column as text in the excel file to resolve it being imported as double.
- The I found a piece of code that will make it primary somewhere (see below)

Thank you for your help!

Sub ImportExcel(iName As String)
   
    Dim username As String
   
    username = Environ("USERNAME")
   
    On Error Resume Next
    CurrentDb.Execute "DELETE * FROM [TEMP]"
    On Error GoTo 0
   
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TEMP", "C:\Users\" & username & "\Desktop\" & iName, True
   
    DoCmd.RunSQL "CREATE INDEX ind ON [TEMP] ([IDNUMBER]) With Primary"
End Sub

Author

Commented:
Got it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial