compare excel and access table and update changes

[anand padia]

I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master.

Now I want to develop a excel application that will import all the records from access table or some particular data (defined by field id) in excel spreadsheet. (***successfully acomplished)

User would be able to view the records and make changes to the spreadsheet now when the user closes the file excel will ask if records needs to be updated to the master, if user selects yes I need a VBA code to run through a check which will compare the master with spreadsheet and update the records.

I know I can link the excel spreadsheet and do this easily, however we use this in our office through our network and there are about 50 people who could need to update it at once. Now excel spreadsheet cannot be opened and updated by all at once.

Please let me know if there is a method through which I can use to do this.

Access Master Records have employee info. like EMPID, Name, Manager Name, Date of Joining, Designation, Email.

[NeoPa]

This sounds like a complicated setup. I would advise against using one such as this as there are so many complications involved, such as which update to keep when there is more than one from the many instances.

That said, the easiest and most reliable would clearly not be a simple Excel process. The Excel part could export a file for the Access part to import. It could even trigger the Access code to start by opening the database. It would be sensible to include the date/time in both the master and the Excel tables so only the latest would be used. Clearly only the updated records should be included in the exports.

[anand padia]

I was able to achieve little bit...

Sub UpdateMDB()
Dim cn As Object
Dim rs As Object
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * FROM [Sheet1$] s " _
    & "INNER JOIN [;Database=D:\temp excel\T.mdb;].Table1 t " _
    & "ON s.id=t.id " _
    & "WHERE s.Field1<>t.Field1"
rs.Open strSQL, cn, 1, 3
strSQL = "UPDATE [;Database=D:\temp excel\T.mdb;].Table1 t " _
    & "INNER JOIN [Sheet1$] s " _
    & "ON s.id=t.id " _
    & "SET t.Field1=s.Field1 " _
    & "WHERE s.Field1<>t.Field1 "
cn.Execute strSQL
End Sub

Above code compares the sheet data and writes it however does not update if any new records are updated in excel.

Is there a way to have excel to update any new data found using the same batch technique. Also can I incorporate it in the below mentioned code in any way.

[NeoPa]

I would make a few suggestions.

1. Always declare your procedures as Public or Private explicitly (Line #1).
2. Wherever possible, declare your variables explicitly (Lines #2 & #3).
3. Avoid use of numbered indices with collections (Line #4). It is harder to read, less obvious what the code is doing.
4. Use indentation. This helps a reader to make sense of what your code is doing.

A revised version of your code below to illustrate these points :

Public Sub UpdateMDB()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
 
    strFile = ActiveWorkbook.FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & _
             ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open strCon
    strSQL = "SELECT * FROM [Sheet1$] s " & _
             "INNER JOIN [;Database=D:\temp excel\T.mdb;].Table1 t " & _
             "ON s.id=t.id " & _
             "WHERE s.Field1<>t.Field1"
    rs.Open strSQL, cn, 1, 3
    strSQL = "UPDATE [;Database=D:\temp excel\T.mdb;].Table1 t " & _
             "INNER JOIN [Sheet1$] s " & _
             "ON s.id=t.id " & _
             "SET t.Field1=s.Field1 " & _
             "WHERE s.Field1<>t.Field1 "
    cn.Execute strSQL
End Sub

To handle new records added to the Excel sheet, consider changing the last SQL to start with [Sheet1$] then use a LEFT JOIN to match up with [Table1]. If ADODB supports OUTER JOINs (SQL Server does but I don't think you can with Jet) then use that instead, and you can handle deleted records too.

[NeoPa]

Sorry.

Forget the last suggestion. I overlooked the point that it was an UPDATE query. You can only update existing records. Separate INSERT and DELETE queries would be required to do the job completely.

[anand padia]

@NeoPa
Thanks will try that. However when in terms of going for a separate INSERT and DELETE queries I know how to do that individual, but how to compare it with existing records on excel.

[NeoPa]

I presume you would INSERT any records that are in your workbook, but not already in [Table1]. DELETE should be for those records which are currently in [Table1] but which aren't in your workbook.

[anand padia]

@NeoPa
Yes you are absolutely right. Do you think it can be done...

[thelonelyghost]

There's no chance of running a small SQL server of sorts? What about attaching it to an existing server? If you are using an MDB file, they are definitely not made to be used by multiple users at the same time, whereas SQL tables are.

EDIT: I retract this statement of multiple concurrent users not being a part of MDB files. See post below

Also, if you go the route of SQL, this would save a lot of effort on your part in designing it and make it more versatile in what it can update. For instance, if you decide to move the employee records to some global corporate database (which could use Oracle, SQL server, etc.), it's just a nip and a tuck to do so with this. With your method it seems like it would take a total body reconstruction.

[NeoPa]

thelonelyghost: If you are using an MDB file, they are definitely not made to be used by multiple users at the same time, whereas SQL tables are.

While Access databases are not able to handle multiple concurrent users as well as full, server based, solutions are, they are nevertheless, designed to handle multiple concurrent users. You may certainly find that the practical limit can be exceeded well before something like SQL Server, but that is some way short of meaning multiple users are not handled. It does depend on correct configuration for them of course.

[NeoPa]

anand padia: Yes you are absolutely right. Do you think it can be done...

I see no reason why not, assuming you have a clear understanding of exactly what you're hoping to do with it.

[thelonelyghost]

@NeoPa
You're right. That was my mistake. I revisited my source for that bit of information and it said something like 20 users max (depending on what you're doing), which was significantly less than what I needed. I therefore wrote it off as useless for multiple concurrent users in favor of something most bigger corporations have and use: SQL Server (or the like). Once again, my bad and I recant my previous statement.

[NeoPa]

No fuss :)

Like you, I wouldn't consider it for a big, concurrent multi-user project, but there is some limited scope there. This can be important for small office concerns where they are nervous of investing time and/or money into two different systems. Unfortunately, most such users struggle with configuring it correctly for concurrent multi-use anyway :(