Merging Excel Information to Access Table (1 Viewer)

[naiasuszek]

Hello,

I have an Access table full of customer contacts. I recently scanned in a few hundred business cards and stored the information in Excel. The Excel columns are in the same order as the Access fields. I would like to add to the Access table. How can I do this?

Thanks, Naia

 

[Mr. B]

If the columns match the Access fields, or even if they don't exactly match, you should be able to use the "Import" option from the "File" menu to import the data. You might want to just import the data and let Access create a new table for the incomming data. Then use a query to add the incomming data to the existing table of data, just as a saftey thing.

HTH

 

[seopositive2]

You'll need to check through the tables in the current database.

Perhaps you could try this, totally untested, code.
Function ImportTable(bpath As String)
Dim db As Database
Dim tdf As TableDef
Dim rdfCur As TableDef
Dim TableExists As Boolean
Dim obj As AccessObject, currentdb As Database

Set db = OpenDatabase(bpath)
Set currentdb = Application.currentdb

For Each tdf In db.TableDefs
TableExists = False
If Not (Left(tdf. Name, 4)) = "MSys" Then
For Each tdfcur In currentdb.TableDefs
If tdf = tdfcur Then
TableExists = True
Exit For
End If
Next

If Not TableExists Then
Access.DoCmd.TransferDatabase acImport, "Microsoft Access", bpath, acTable, tdf.Name, tdf.Name, False
Else
' code to append table to existing table.
End If

End If
Next tdf

Set db = Nothing

End Function

 

[naiasuszek]

Mr. B,

When I try importing the table I get this error: An error occurred trying to import file 'C:\location.xls'. The file was not imported.

If I decide to create a new table and query them together; how could a create a query to permanently merge my new table to my old table?

Thanks, N

 

[Mr. B]

naiasuszek,

As for the error, I'm afraid that that does not tell us much, does it. If you cannot get it to work, you may have to post the Excel file here and let one of us try to import it or perhaps see what is wrong. You do not have to leave all of the data in the sheet, just leave say 4 or 5 rows of data.

You would use an Append type query. That query will allow you to map one file in one table to a field in the other table. You have to be sure that you map the fields in the imcomming data to the correct data types in the new table.

HTH

 

[neileg]

I suspect you have a datatype problem. Excel doesn't care about datatypes, you can have any old rubbish in your columns. Access is fussy about datatypes. When you import data, Access guesses what the datatype is based on the first few records. If the guess doesn't fit the rest of the data, you get errors.