Solved

access questions

Posted on 2014-12-03
3
58 Views
Last Modified: 2016-07-11
1)what is staging table?
2) how do i write a vba code with that import an excel file into a table with a message box "import was succesfull"?
0
Comment
Question by:akinamon1
3 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 40480154
1) A table is a SQL used when importing external data. It isn't an Access feature or concept.
2) That very much depends on the scenario, here is some generic code which should get you started:

Sub Demo

        Dim strFile As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in EXCEL worksheet
        ' has field names
        blnHasFieldNames = False

        ' Replace tablename with the name of the destination table name
        strTable = "tablename"

        ' Change C:\Temp\Temp.xlsx with the full file name and path of the Excel file
        strFile = "C:\Temp\Temp.xlsx"

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

End Sub

Open in new window

0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40480229
1. A sort of temporary table. In most cases, you won't have a need for it.

2. Often the best method is not to import the Excel data but link them. Then create a simple select query that pulls the data from the linked table and performs basic filtering for empty records/fields and conversion (like converting text fields to numeric or date) which you would use in any case where these data will be used in your application. Also, alias can be applied to field names, indeed where you have none and the have received the default F1, F2, etc.

Now, use the query from #2 as the source whenever you need to pull data from the Excel file.

/gustav
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question