Update table in access from excel

[Boss]

Hi,

I have a situation where my team (50 people) update excel sheet in their own
systems. What i wish to do is to get entire data in one master table in MS
access. I can do it daily by importing multiple excel sheets.

But i need to view the entire data at runtime. All i should do for this is
that link access table at the backend so that when the user submits one
record in his excel sheet record gets updated in the master table of Access.


I know this is possible but could not write the code find it in google.
please help me with the code, This would help me a lot in the project.

Thanks!
Boss

 

[Joel]

I find there are good example in the Access VBA help. Below is one of them.
This code will run in Excel VBA by adding two references in Excel VBA from
the menu tools - References. You can use either DAO methods or ADO methods.
The active X below is the ADO (Active X Data Object)

Microsoft Access 11.0 library object
Microsoft ActiveX Dataobjects 2.8 library


Use the latest version on your computer depending on the version of office
installed. You can modify the code below as required. The code below shows
how to open the database

DAO Open code

Dim dbsExample As Database
Dim rstExample As Recordset
Dim fldExample As Field

Set dbsExample = OpenDatabase("Biblio.mdb")
Set rstExample = dbsExample.OpenRecordset("Authors", _ dbOpenDynaset)
Set fldExample = rstExample.Fields("Au_ID")

Authors is the Table in Access and Au_ID is one of the columns in the table.

ADO code below
----------------------------------------------------

Public Sub OpenX()
On Error GoTo ErrorHandler

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable

' Assign the first employee record's hire date
' to a variable, then change the hire date
varDate = rstEmployees!hire_date
Debug.Print "Original data"
Debug.Print " Name - Hire Date"
Debug.Print " " & rstEmployees!fname & " " & _
rstEmployees!lname & " - " & rstEmployees!hire_date
rstEmployees!hire_date = #1/1/1900#
rstEmployees.Update
Debug.Print "Changed data"
Debug.Print " Name - Hire Date"
Debug.Print " " & rstEmployees!fname & " " & _
rstEmployees!lname & " - " & rstEmployees!hire_date

' Requery Recordset and reset the hire date
rstEmployees.Requery
rstEmployees!hire_date = varDate
rstEmployees.Update
Debug.Print "Data after reset"
Debug.Print " Name - Hire Date"
Debug.Print " " & rstEmployees!fname & " " & _
rstEmployees!lname & " - " & rstEmployees!hire_date

' clean up
rstEmployees.Close
Cnxn.Close
Set rstEmployees = Nothing
Set Cnxn = Nothing
Exit Sub

ErrorHandler:
' clean up
If Not rstEmployees Is Nothing Then
If rstEmployees.State = adStateOpen Then rstEmployees.Close
End If
Set rstEmployees = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub

 

[Boss]

Thanks for the reply Joel... This was excellent...

Boss

 

[Boss]

Hi,

I am facing a new problem, With the data which is getting updated in table i
have prepared a form in ms access which give me counts of the activities.

As i save the ms access form it gives me a error: Database in state by user
"admin". Then i cannot update records using excel.

please help.
Thanks!
Boss