Excel form to save data in Access

[nadz84]

I have the below code for an excel form to currently save data in another excel.the only issue is that this doesn't work properly if more than 1 person are trying to send the data. Is it possible to make the code in a way that it sends data to an access db? Here my code.
Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim historyWb As Workbook '<~ target workbook

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"

Set inputWks = Worksheets("Input")
Set historyWb = Workbooks.Open("C:\reports\consolidated.xlsx") '<~ open target workbook and assign sheet
Set historyWks = historyWb.Worksheets("PartsData")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

historyWb.Save '<~ save and close the target workbook
historyWb.Close SaveChanges:=False

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub

[Kenneth Hobs]

Welcome to the forum! Please use code tags when posting code.

Use ADO. Here is a link with examples. http://www.erlandsendata.no/english/index.php?t=envbadac

[nadz84]

Hi. I read the link thank u, but all im finding is how to transfer data from a file (export) to access
What im looking for is when I click my "submit" button for example, data from certain fields are saved in access db instead of another excel fil, which can be found in the code above.
Thanks

[nadz84]

Anyone :)

[Kenneth Hobs]

You seem to be saying the same thing and I gave you the answer. Read the links there or search here or other places for ADO. There are many examples.

To get specific help, we need specific data and or details. If you can not explain your needs fully, simply attach very simple files for us to illustrate a solution. The key for you I expect, is adding the correct SQL string.

Of course there are other methods like QueryTable that might suffice. Here again, it is a matter of getting the details right. It would use an SQL string as well.

Here is just link of many that I have posted on this forum. http://www.vbaexpress.com/forum/showthread.php?38073