visual basic in Access

Hello, i have a access db and i want to copy the contents of an excel file and paste it into a table in the db. The copy-paste procedure is prefered, because when i import the excel file into the table, the records with error concerning referentiel integerity or key fields don't come along. And if i use the copy-paste method, i get a temperary table with past errors , so i can see what the records are in the excel file, that didn't came along.

as you can with most of the office objects, you can instantiate an Access object, and one of the methods of that object is to copy from an xl spreadsheet to a table in your database. I use it a lot to populate "Staging Tables" in my Access Databases so that I can then populate the relational tables without my database "blowing up" . It's a really helpful tool... here's an example.



for this example to work, you'll need a project with references to the Microsoft Access Library, and Microsoft ADO Library

Use TransferSpreadsheet method:
TransferSpreadsheet Method

The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

expression Required. An expression that returns one of the objects in the Applies To list.

TransferType Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access. You can import from and link (read-only) to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using this method.

If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed.


TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

UseOA Optional Variant.

Remarks
For more information on how the action and its arguments work, see the action topic.

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Note You can also use ActiveX Data Objects (ADO) to create a link by using the ActiveConnection property for the Recordset object.

Example
The following example imports the data from the specified range of the Lotus spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses the first row of the spreadsheet as field names.

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

On msdn page:http://msdn.microsoft.com/library/d...spreadsheet.asp

Thanks for backing me up, CP

Thanks you guys for replying, but i've tried the usetransfersheet method and it imports the contents of the excel sheet to the table. With this procedure i don't get a table with errors with records that aren't imported ( error concerning referential integerity). And if i use the copy-paste method , i get a temperary table with records that aren't pasted.

It'swrong that the imported table is not integrated??

there is nothing wrong with the usetransfersheet (import) method, but because i am importing the excel sheet into an table that has relationsships with other tables in the db, i expect to get errors concerning fields in the sheet and i would like to get the errors in a seperate table. So i can fix the errors an correct the import.

xy - I wasn't saying to import into your existing relational table. You can import into a new table and use it like a staging table. I call mine tblTemp. Rather than trying to import into the existing relational tables, I dump all of the data I want from the excel spreadsheet into a temporary table ("tblTemp"), and then use SQL Commands to process that data into my existing relational tables. Then, at the end, you can delete your temporary table, and you're left with an updated, relational database

You're the (Fisher)man. Ofcourse i can do that , why didn't i think of it in the first place.
Thank you