I have experience with Excel but not much with access. I am trying to create a "Database" where bunch of similar data from many workbooks is kept together in one file in MS-Access.
All the workbooks are made up of many worksheets, but all of them include a "Data" worksheet which data gets inserted into with macros from all the other worksheets in the same workbook.
This data sheet is the same for all the workbooks, made up of headers starting in cell A1 and all the way to BL1. Data gets inserted starting with A3 (so A3 to BL3). Data is inserted with a macro as a whole row(A3-BL3 all together).
CURRENTLY:
The code I am currently using is below, which transfers the whole worksheet set up on a timer(every 12 hours), but I am trying to find a better solution to achieve my goal.
PROBLEMS CURRENTLY: 1) It is too slow, I have over 10,000 rows of data, and trasnferring the whole worksheet every 12 hours is not the fastest or best method (I really dont like doing it this way). 2) Excel workbook is always open and in use, so when timer kicks in and it is running the Excel-to-Access Macro this could cause confusion to the user and/or crash(I do not even want the user to be aware of this access database)
GOAL:
Have this macro run from Excel, every time new data row is created in the Data worksheet(A6-BL6), and have it inserted into the access database table without deleting anything else(basically inserting into access 1 row every time new row of data is created in the Data worksheet in excel. I want it to push old data down in access so everything is kept and nothing is overwritten or lost.
It is crucial I run the macro from excel and not access(dont even want access to be accessible or opened by other user just want data stored there)
Excel and Access are both 2016.
If anyone could please help and/or give me suggestions I would appreciate it, have done some research but haven't found anything that solved my problem completely.
Option Explicit
Sub AccImport()
Application.OnTime Now + TimeValue("12:00"), "AccImport"
Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Users\yilly1\Desktop\Database.accdb"
acc.DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:="Workbook1", _
Filename:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Data1$A1:BL60000"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub