Multiple writes from Excel to Access

[basil3legs]

Hi,

I have an Excel spreadsheet which writes loads of data to an Access database and takes ages to run as most of the information is downloaded from tables on the internet. I have found that if I split the spreadsheet in 2 and run both copies at the same time with each doing half the work, it runs a lot quicker as it can access the internet quicker (which takes most of the time). However, every so often one or both copies will crash saying the database is already in use (obviously by the other spreadsheet running). Sometimes both will say the file is in use at the same time and I cannot restart either or even open the database in Access as that says the same, only a reboot will help!!

An example function is:

Function AddData(ID, Name)

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\databases\database.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    With rs
        .AddNew ' create a new record
        .Fields("Name") = Name
        .Fields("ID") = ID
        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Function

So the database is only open for a short time for each write (but there are a lot of them!!).

Is there a better way of doing this to prevent this happening as I thought Access could handle multiple writes at the same time.

Thanks in advance.

 

[PHV]

Have you considered the TransferSpreadsheet method in Access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Just a suggestion; don't know if this will solve the problem or not but should be easy to implement: Only create/open a connection to the database once in each instance of your Excel workbook, closing it after all data is transfered. I'm assuming it is a collision during this phase that generates the error you're seeing.


Regards,
Mike

 

[basil3legs]

PHV I think that will be of no use to me as Excel is doing all the work, Access is only holding the data.

rmikesmith, now that's a thought. I originally wrote it to keep the connection open only for short periods when writing to try and avoid this type of problem as much as possible. I will certainly try it and post how it goes.

Thanks both for the help.

 

[basil3legs]

Well, I have tried rmikesmith's idea and it seemed to help with the database locking problem (or at least this week it did as it tends to be a bit intermittent but hopefully it does) but there is a major problem with it!!

If a record does not exist, the record is added, if it already exists, the relevant field is edited. However, with not closing the database, if it looks for the same record again soon after, it still does not see it so adds it again! Is there a way of getting Excel to see the updated database without closing it each time?

 

[rmikesmith]

Can you post the relevant (revised?) code to open the recordset and add/edit a record?

If a record does not exist, the record is added, if it already exists, the relevant field is edited. However, with not closing the database, if it looks for the same record again soon after, it still does not see it so adds it again!

Is this a case of Workbook Copy1 not being able to see changes (e.g. added record) made by Workbook Copy2?


Regards,
Mike

 

[basil3legs]

Hi Mike,

By the looks of the data, it appears Copy1 couldn't see it's own changes it had just made as often data needs writing to the same record (but different fields) consecutively 6 or 7 times and there were up to 6 or 7 duplicates of some records and as there is no set order for the data, the record's existance has to be checked each time.

I originally took every occurance of:

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data 
.
.
.
    cn.Close
    Set cn = Nothing

out and just put it as the first and last thing done when the main macro is run and declared cn as public. So AddData was as before but with all reference to cn removed. There are loads of functions accessing the database, reading, writing and editing records, AddData was just an example but one of the most important.

I then had the bright idea of closing and reopening the database at the end of AddData but this fell over as the procedure calling AddData had a record open which it then tried to close after AddData had killed it's connection (OK, so I could have error checked this!!).

The current state of play is that the database is opened and closed as the first and last thing done when the main macro is called and AddData opens a second connection, cn2, which it closes after adding the record. So basically back to the original AddData code but with cn2 instead of cn. So far this seems to be working with no real problems so far although it was very unpredictable before.

I also have various ASP pages that query the database and these have had no problems at all (amazingly, even when 2 copies of Excel are writing to the database at the same time!!) since the first changes as suggested by you so there is definitely a big improvement so thanks again.