From Excel, Open Access, Copy a cell to Access, close Access

[FTM]

Hello Everyone,

I want to open Microsoft Access Database using VBA from an MS Excel Spreadsheet, open a data entry form then add data to the existing database. When I have exhausted all of the lines on the spreadsheet then I want to close MS Access.

What is happening is that I have a spreadsheet that contains daily production data per part number. I want to append that data to a spreadsheet at the end of the production shift. In the past I have used the Macro Editor that is native to MS Excel and life has been good. In the event that I can't use the editor I have been quite adept in pirating code from sundry forums on the web.

Now...

I consider my "Google-Foo" to be at or above average and I've crawled all over google every way that I can think of and as of yet I have found no mention made at all of appending a database from within MS Excel. I don't write VBA code very often so most every time that I try I have to relearn most things. Is there anybody that has tried this before? Is manipulating an Access database from an Excel VBA script even possible?

Regards to everyone and thanks in advance for any assistance that can be offered.

 

[xenou]

Hi,
The relative references thing would only apply to recording macros (we aren't doing that - we're running a macro that we're writing ourselves).


This looks wrong:
'//Loop rows and create rest of the SQL Statement, then run SQL commands
For i = 2 To rng.Rows.Count
rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(2).Value
rs.Update
Next i


The Cells property is meant to carry us through the cells in the row, so it should be incrementing as we go:

'//Loop rows and create rest of the SQL Statement, then run SQL commands
For i = 2 To rng.Rows.Count
rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(3).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(4).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(5).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(6).Value
rs.Update
Next i

 

[FTM]

xenou,

You are right, I made the edit that you suggested and everything works right up to the "rs.Update
" statement. The reason that I get the error that I'm getting is that there's no data being written to the database. The VBA program is stepping through the cells in the database but not writing anything to a cell in the database. How do I read or populate the "rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value" from information contained in the spreadsheet?

I know that there is no data contained in "Part" because I set a watch on "Part" then stepped thrrough the program.

I have a couple ideas on how to continue from this point but I'll tell you up front, I don't know what I'm doing. If you can help me out with this oone last item I swear I''ll stop bothering you.

Thanks, and best regards,

FTM

 

[FTM]

xenou,

I appologize again, the shift has been chaotic.

There's the current code set:

Option Explicit
Sub Test2()
'
' Test2 Macro
'
Dim rng As Range
Dim s(1) As String
Dim SQL As String
Dim i As Long
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Inventory Report").Select
Range("A2").Select

'//Open Database
Set db = DBEngine(0).OpenDatabase("C:\users\220042816\My Documents\Microsoft Access\Inventory Report\Inventory Report.Accdb")
Set rs = db.OpenRecordset("Inventory Report")

'//Range of values to update
Set rng = Sheet1.Range("A2:F10").CurrentRegion

'//First Half of SQL Statement
s(0) = "INSERT INTO Table1 (Part, Description, Pack Quantity, Box Count, Odds, Actual Inventory) VALUES ( "

'//Loop rows and create rest of the SQL Statement, then run SQL commands
For i = 2 To rng.Rows.Count
rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(3).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(4).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(5).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(6).Value
rs.Update
Next i

'//Close database
db.Close
Set db = Nothing

End Sub

I've been working on the problem as I have time. The first question that I have has to do with the "Dim s(1) As String" statement. Later on in the program there is a statement that says "s(0) = "INSERT INTO Table1..." shouldn't S(0) actually be S(1)?

Another question that I have, the statement "Set rng = Sheet1.Range("A2:F10").CurrentRegion." In the spreadsheet where the data originates, the name of the spreadsheet is "Inventory Report." The range of the data is from A2 to F10. The Headers that occupy A1 through F1 are "Part, Description, Pack Quantity, Box Count, Odds and Actual Inventory."

As best as I can tell, the program is opening the Inventory Report database and opening the Inventory Report Table. The inventory Report spreadsheet is already open. I added three lines of code to move to the A2 cell on the Inventory Report spreadsheet page on the Inventory Report Spreadsheet in the event that anotehr cell on another spreadsheet page was activated, if this makes a difference. I.m sorry that "Inventory Report" shows up so many times, Requirements keep popping and I don't understa nd the ramifications of what I name files till it's later in the project.

Right now the program is stopping on the "rs.Update" line. The error that I get is on a dialog box that says "RuntimeError 3314" and then "You must enter a value in the "Inventory Report.Date" field." This is a different error that what I have seen before.

I'll keep slugging away at the problem. Thanks for the help.

Regards,

FTM

 

[FTM]

xenou,

I added a line in the for/next loop that sets the date equal to the system date. As follows: "rs.Fields("Date").Value = Date()." The data from the current report is now added to the database real slick. Eurika!

I need to add an "if" line internal to the for/next loop to exit the loop in the even that a blank cell is encountered.

You've been a huge help! I'll work on a loop exit unless you want to throw me one.
Thanks for all of your assistance!

Regards,


FTM

 

[xenou]

Hi,
Great. You've done really well working this out this much. Good job.

For your question about the blanks. If you want to stop when a blank cell is encountered, you can do that with an IF statement, as you suspected. I think like this:

'//Loop rows and create rest of the SQL Statement, then run SQL commands
For i = 2 To rng.Rows.Count
[B]    If Len(rng.Cells(1,1).Value) <> 0 Then[/B]
        rs.AddNew
        rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
        rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
        rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(3).Value
        rs.Fields("Box Count").Value = rng.Rows(i).Cells(4).Value
        rs.Fields("Odds").Value = rng.Rows(i).Cells(5).Value
        rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(6).Value
        rs.Update
[B]    Else
        Exit For
    End If[/B]
Next i

The only assumption here is that a blank cell in column A means the whole row from A to F is blank and we just quit at that point.



Some cleanup items:
With my statement Dim s(1) As String I am creating an array. So we can store strings in a(0) or a(1) -- arrays are zero-based so s(0) is the first element in the array, and a(1) is the second. As far as I can tell you only use one string in this code, so you can store it in either memory location (!). I guess that makes it easier. We really did not need an array if there is only one string we are storing, but it does not hurt anything so you can not worry about it -- all is well.

The use of CurrentRegion is a trick. If we use Range("A2").CurrentRegion we get all the cells that are "touching" A2. This can be a quick way to select a table of data. If you know that your data is in A2:F10 and only in A2:F10 you can dispense with the CurrentRegion stuff altogether and just write Sheet1.Range("A2:F10") instead of Sheet1.Range("A2:F10").CurrentRegion.

 

[FTM]

Last item and this dog is hung...

Everything is running as advertised but the script isn't closing the database. I open the folder where the database resides and the .laccdb file is still open and doesn't close.

The data in the database is good, there are nine rows of new data. If I can get the script to close the database, show's over, nothing more to see here folks.

This is great, I've been working on this problem for at least a month now. Thanks you for your assistance! This is a really big deal.

Regards,

FTM

 

[xenou]

Replace:

'//Close database
db.Close
Set db = Nothing

End Sub

Instead:

On Error Move Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Not sure if it will work but I always try to close objects I opened. It's not supposed to matter but DAO doesn't have a good reputation in this regard.

 

[FTM]

xenou,

I ran into a problem that I didn't anticipate. All of the code that we have mentioned beforehand executes without error.

With MS Excel still running, minimized in the background, I open the folder with the MS Access database. When I attempt to open the database I get the additional .laccdb file which is locked and I can't open the database.

I'm thinking that I need to pause the MS Excel VBA script and allow MS Access to close out, maybe. Or perhaps configure the database to allow more than one user, I don't even know if this is possible.

I'm going to do some more research on the topic. I remember that you mentioned that you were doing some SQL manipulations. Is there an SQL command string that we need to send in order to close out this database?

Thanks,

FTM

 

[FTM]

xenou,

I probably should have mentioned that if I return to the VBA program in the MS Excel from thje file folder where the MS Access database resides and cllick on the stop button on the task bar the .laccdb file closes and then I can access the database.


Any ideas?



Regards,



FTM

 

[xenou]

To my mind, your code just run almost instantly. It is true that the database should be configured for shared use (as a general rule). Make sure that is the case -- it's just an option you select in database options. Closing the recordset and exiting the sub should be enough to release the handle on the database.

BTW, if you have a linked Excel sheet as a table in the Access database that's going to lock the database.