0

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
DYilm
  • 35
  • 1
  • 7
  • I think instead of transferring the whole spreadsheet the entire time, you should find a way to keep track of the last record inserted into access from excel, then when there are worksheet changes to the 'main' sheet, use [an adodb query](https://stackoverflow.com/questions/12979605/how-to-retrieve-data-from-excel-with-adodb-connection-if-the-first-line-of-the-w) to only insert just one row at a time – Marcucciboy2 Jul 30 '18 at 17:36
  • @Marcucciboy2 that is actually exactly what I am trying to do, insert one row at a time to access each time new row is recorded in excel(I can keep track of this row creation on excel). My plan was to have this excel-to-access macro run right after the new record macro is run on excel but I am not sure how to use the adodb query for this to just transfer 1 row from excel to access. – DYilm Jul 30 '18 at 17:47
  • 1
    This is really easy with ADO. From excel define a connection to your Access database and then run a SQL query to add one or multiple rows from Excel to Access. Once the appending is done do an update on the excel side, for example create a column and mark it as transferred to Access etc that column will help you determine which rows should go to Access if you run the query the next time – Ibo Jul 30 '18 at 18:20
  • Here's a good site for learning about ADO https://msdn.microsoft.com/en-us/library/ms524771(v=vs.90).aspx – Marcucciboy2 Jul 30 '18 at 18:21
  • with ADO you can define a sheet or a range in Excel as your database, but since you need another connection to Access it will not work for you to use 2 connections to run one single query so your best bet would be to create a connection to your excel range, run a query which will give you a recordset of the rows that should go to Access, then create another connection to access and run a simple select all query, – Ibo Jul 30 '18 at 18:29
  • your connection should NOT be a read only connection so you have to establish a connection that allows you to write on the recordset: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-2017 once done, you can save the query and close the connection and you access DB should be updated, then you need to flag your excel column as update. remember you need to make a loop to update a recordset, – Ibo Jul 30 '18 at 18:29
  • if you really want to update one row at a time I guess you don't need to get the data from excel using SQL and you can simple read data and update the recordset connected to your access DB – Ibo Jul 30 '18 at 18:32
  • @Ibo thank you for your comments, I MUST run the code from Excel and not access, and I also still need to keep the records on excel table as well. I am just trying to make a copy of the new row that is created and moved it to access by running a macro through excel. Is this still possible with ADO or does it only work if ran from access? – DYilm Jul 30 '18 at 19:43
  • @DYilm all my comments considered that you run it from Excel and Excel only. If your records are not too many say under 300-400k, then you can always simply replace the access table with the updated data from Excel. I just don't understand why you want to have a copy in ACCESS and not use it for anything. That would be a simple backup that you can do it once per day etc regardless of the number of rows you have and it would be a lot simpler – Ibo Jul 30 '18 at 20:59
  • @Ibo there are a lot of different users using the excel files, however I want to be able to put the data of all the excel files together on access so I can study this data on my own as a whole without having to open each individual excel file to look at the data. – DYilm Jul 31 '18 at 20:33
  • @DYilm that is fine! remember to close your connection from Excel to Access once the SQL operation is done, ACCESS may not be able to handle properly more than 25 connections together, there is no official limit or sth, but I have heard 15-25 is kind of the limit to handle at once – Ibo Aug 01 '18 at 02:36
  • @Ibo thank you, I still have not been able to figure out the code to update an ADO query, I have been struggling to figure this out and I have been doing a lot of reserach. There are 64 columns on my data table and this has given me a lot of trouble – DYilm Aug 01 '18 at 14:21
  • @DYilm here is the best way you can do, just find a code that connects Excel to Access and runs a SQL query, google it, even MSDN has many examples. Then, based on your own problem change it and if you had a problem post a new question, paste the link here and we will have a look at it. You need to create a reproducible demo problem – Ibo Aug 01 '18 at 16:09
  • @DYilm you need to break your problem into smaller pieces and solve it one by one, you cannot solve the whole thing in one question – Ibo Aug 01 '18 at 16:10
  • @Ibo I have posted a new thread with my progress and I am currently stuck, here is the link if you could please take a look. thank you https://stackoverflow.com/questions/51750880/excel-vba-to-append-data-to-access-table-with-ado – DYilm Aug 08 '18 at 15:53

0 Answers0