Import excel sheet data into Ms-access using VBA macros into split Db

[mond007]

Hi
I have seen the following post which works great.
https://www.accessforums.net/showthr...+access+tables

Private Sub cmdbImportData_Click()
 
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
Dim strPathFile As String
Dim oTable As DAO.TableDef
 
On Error GoTo ImportXLSheetsAsTables_Error
 
strPathFile = "C:\BP-RADS-One-Source_LIVE.xlsx"
 
CurrentDb.Execute "DELETE * FROM Tbl_Apr 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_May 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jun 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jul 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Aug 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Sep 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Oct 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Nov 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Dec 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jan 2017;"
CurrentDb.Execute "DELETE * FROM Tbl_Feb 2017;"
CurrentDb.Execute "DELETE * FROM Tbl_Mar 2017;"
 
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open(strPathFile)
 
For Each sh In wb.Sheets
Debug.Print sh.Name
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_" & sh.Name, strPathFile, True, sh.Name & "!"
Next
 
wb.Close
appExcel.Quit
 
For Each oTable In CurrentDb.TableDefs              'Lets Clean up the tables.
If oTable.Name Like "*ImportErrors*" Then
    CurrentDb.TableDefs.Delete oTable.Name
End If
Next oTable
 
MsgBox "12 months RADS Tables created."
 
On Error GoTo 0
Exit Sub
 
ImportXLSheetsAsTables_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

The problem is I have a split Db and when I run this Macro the Tbl_..... are created in the front end and not the back end.

I can not run the macro in the BackEnd as I don't want anyone to access it owing to locking issues. The macro code has to live in the front and and populate backend tables.


Another most important twist is that the tables then need to be linked into the front end. I suppose I can do that as a one off.

I should imagine that I need to point to the BackEnd Db in order to create Tables but not quite sure syntactically this can be done ?

Would appreciate some guidance.
Thanks in advance

[JoeM]

I actually see a fundamental problem with the logic of the structure of the database. In a production database, you typically should not be adding new tables to it all the time. You should be importing data into existing tables. It is also not really best practice to have many tables having similar structures.

Since all your monthly tables probably have the same structure, what you should really have is just one table that you are importing data into. Then, just have an additional field in that table to indicate what month the data is for. You could so that by after importing each sheet, run an Update Query which pulls the sheet name and populates the new field with this value.

[mond007]

Firstly, I am simply refreshing the 12 tables with new up-to-date data.
Even though I feel its cleaner my way, somehow I tend to agree about loading into one table with a date field but when I consider my ultimate goal is to create a summary Crosstab Query to end up with the following :
(Dept and Heads are two fields with all the tables). This is clear headcount data).

Dept, Apr Sum(Heads) 2016, May Sum(Heads) 2016, Jun Sum(Heads) 2016, Jul Sum(Heads) 2016, ... etc

I am currently trying to create the crosstab Query (with not much luck) to summarise the count of Heads by month by Dept.

A bit more involved than I thought. : - (

The challenge is on I guess.

[JoeM]

The problem is I have a split Db and when I run this Macro the Tbl_..... are created in the front end and not the back end.

Firstly, I am simply refreshing the 12 tables with new up-to-date data.

I am a little confused. If you are simply updating existing tables with new up-to-date data, what tables are being created on the front-end?
If you are simply updating data in existing tables, no new tables should be being created (except for error tables, which can be deleted, like as shown in your script).

[mond007]

Ah Ha... yeah I see why your confused...

I am trying to get the tables loaded daily in the backend but the macro that does that needs to sit in the front and as its the admin staff who have to press the "Button" to do the reload of the tables from the Excel Load file.

I can see this is too much hassle.

I think for me I am going to summary the data in Excel from the 12 months using sumifs forulas and simply pull in the Summary page as a Linked worksheet. Job done. Trying to create crosstab queries to summarise is a lot harder.

So I think I am going to change tack-tics as sometimes in these types of scenarios.

Many Thanks for your help ... its made the direction clearer for me. : - )

[JoeM]

You are welcome.

I am trying to get the tables loaded daily in the backend but the macro that does that needs to sit in the front and as its the admin staff who have to press the "Button" to do the reload of the tables from the Excel Load file.

Just to be clear, in split databases, typically the data Tables reside on the back-end (and are linked to the front-end), while Queries, Forms, Reports, Macros, and VBA all reside on the front-end. So users would import/enter the data from a front-end (whether manually or by a macro). Since the data should be going to existing Tables found in the back-end, there shouldn't be any issues with that. None of the data will be going directly to the front-end only, since the data Tables do not exist on the front-end (only the new error tables would go to the front-end, but your code is deleting those anyway).

I hope that makes sense...

[mond007]

Completely understood. Basically the tables should be created in the back-end and the VBA macro to load them sits in the front and and loads records into the linked table.

Thank you for your insight. The whole process is getting very clear now and it sometimes helps to state one predicament in order to gain some clarity. : - )

[JoeM]

You are welcome! Glad I was able to help!