System Requirements for Complex Large Queries

[nguyeda]

This is my most recent attempt by using my export script from Access to Excel. I'm just trying to reverse it haha



I deleted a lot of the code that I thought was defiantly irrelevent but not all of it obviously.

Option Compare Database
 
Public Function ExportAdvancedExcel()
 
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
 
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
 
strSaveName = "C:\Users\NguyenDH\Desktop\Generic SAR\Master.xlsx"
 
Set appExcel = GetObject(, "Excel.Application")
appExcel.Workbooks.Open (strSaveName)
Set wkb = appExcel.ActiveWorkbook
Set sht = appExcel.ActiveSheet
Set Sht1 = appExcel.Worksheets(2)
Set Sht2 = appExcel.Worksheets(3)
 
With sht
 
strTable = "MCHB"
DoCmd.SetWarnings Flase
 
DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:=strTable, FileName:=strSaveName, _
hasfieldnames:=True
End With
With Sht1
strTable1 = "T001W"
DoCmd.SetWarnings Flase
 
DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:=strTable1, FileName:=strSaveName, _
hasfieldnames:=True
 
End With
With Sht2
strTable2 = "UnOblg"
DoCmd.SetWarnings Flase
 
DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:=strTable2, FileName:=strSaveName, _
hasfieldnames:=True
 
End With
 
Set appExcel = GetObject(, "Excel.Application")
End Function

[rpeare]

I made a test excel file with three sheets (sht, sht1, sht2) put in some bogus data.

Made three tables in access with the names worksheet1, worksheet2, worksheet3, gave them field names identical to the first row on each of my worksheets and used this code and it worked perfectly:

Dim strSaveName As String
strSaveName = CurrentProject.Path & "\TestImport.xlsx"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet1", strSaveName, True, "sht$"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet2", strSaveName, True, "sht1$"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet3", strSaveName, True, "sht2$"

the sheet name has to be encapsulated in "" marks and have a $ mark at the end of the name to tell access you're importing the whole sheet, not just a range of values on the first worksheet.

[nguyeda]

Thanks! That worked!!

But it doesn't work in these scenrios in the future for flexibility:


1) Overwrite the original tables in access OR

2) Update the tables in access if I add/Change:

- Columns
- Column Cell Formats (Number/Date/Text/General/etc)

Ideas? I suppose I could just delete the tables in access and let it reimport everything...

[nguyeda]

Also, during the import or after, can we make 1 of the columns in each table a Primary key?

Edit: May be I should scratch that because the column I use to relate to others is the item number or order number and it's not unique in every table, so I guess that won't work as a primary key?

[rpeare]

There are two ways you can handle the overwrite.

One is to purge the data first (run a delete query on the table) then perform your import.
Two, drop the table and recreate it before each import. This would require probably a little more setup (particularly if you want to make it flexible for future use). For maximum flexibility you would likely have to define the fields and data types you wanted your fields to be in a table and run a procedure that would delete (DROP) the existing table, then recreate it based on the fields of your table creation table.

As far as the autonumber field goes, you should be able to put that on your table and the import will basically ignore the field when importing data. Just add an autonumber field. Just be aware that your data will likely have new primary key fields from week to week so you will not be able to rely on establishing links based on the primary key in access but will have to use the fields that your root database was using to link them.

[nguyeda]

Thanks! What is the syntax for maximum flex? Can you give me a random example to go off of?

[nguyeda]

The queries work significantly faster now by the way

[rpeare]

Here's an example of defining table structure through tables. Just make sure the database and the excel file are in the same folder and open the form, select the file to be processed (note this assumes your files will be named the same each week). I haven't provided for any of the data entry necessary but the structure works.

[nguyeda]

That looks AWESOME. Thanks so MUCH! I think I'll go ahead and mark this as solved!