[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

access questions

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
akinamon1
Asked:
akinamon1
2 Solutions
 
MacroShadowCommented:
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
 
Gustav BrockCIOCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now