update access file from Excel (2000)

Topic

New Reply

I have this macro this import 2 table from an .mdb file, my problem is if i modify a record on one of 2 table of excel is possible to update the .mdb file? Have you a suggestion?
Tks. for all

Sub EstrazioneDati()

‘ — Esatto nome del Database completo del percorso
‘ — nel caso il Database specificato non esista nel percorso, la macro genera un’errore
‘ — verificare quindi l’esistenza del Database

Dim NomeDB As String

NomeDB = “srv04f9494rmtbaspTEMPDBDELIVERY.MDB”

Dim StringaDiConnessione
StringaDiConnessione = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & NomeDB & “;'”

Dim OggettoConnessione As Object, OggettoRecordsetUn, oggettoRecordsetDue As Object
Set OggettoConnessione = CreateObject(“ADODB.Connection”)
OggettoConnessione.Open StringaDiConnessione
Set OggettoRecordsetUn = CreateObject(“ADODB.Recordset”)
Set oggettoRecordsetDue = CreateObject(“ADODB.Recordset”)

‘ — in questa istruzione SQL

Reply | Quote

Replies

Hi Sal,
What you suggest is possible – it would involve using Automation with Excel reaching into Access and using the Access object model. In your situation, it would require some fairly complex code in Excel to detect that a cell in the worksheet had been modified, and then writing the change back to Access. A different approach would be to write out the entire table when the Excel user closed the workbook, but that seems to be pretty drastic, and you will encounter locking problems if two users are working at the same time. Another option might be to actually store the two tables in Excel, and attach the worksheets as tables in Access, but that effectively makes the database a single-user database, since two people cannot edit an Excel workbook at the same time.

Is there a reason not to use Access exclusively? (Most users not having Access installed would be a good one.) I wish I had a better suggestion – perhaps one of the other loungers will have an idea.

Reply | Quote

ok! for help tks.
I have read your suggestion and the solution … “to write out the entire table when the Excel user closed the workbook…” is a good idea. For example insert a button on a sheet and save the data present in the sheet to save all.
If you haev a little time for me write an example macro in this post
Tks.
Salvatore

Reply | Quote

Sorry for the delay in responding – it has been a very busy week. After thinking about what you are trying to do, it seems to me you could do it using the Access ODBC driver and simply connecting Excel to the database. In that case you could simply open up a query using either MS Query, or opening a query within the database – you probably want to avoid using parameter queries – and as long as the recordset is updateable, you should be able to simply edit in the appropriate cell, and have the change made automatically. I’ve not had time to test this, so you should do some experimenting first.

I also should note that I would not recommend rewriting the entire table (as I suggested in my previous post), as that would not be a good thing if two or more people used the database. Sorry about not being clear on that point.

Reply | Quote

Sorry for the delay in responding – it has been a very busy week. After thinking about what you are trying to do, it seems to me you could do it using the Access ODBC driver and simply connecting Excel to the database. In that case you could simply open up a query using either MS Query, or opening a query within the database – you probably want to avoid using parameter queries – and as long as the recordset is updateable, you should be able to simply edit in the appropriate cell, and have the change made automatically. I’ve not had time to test this, so you should do some experimenting first.

I also should note that I would not recommend rewriting the entire table (as I suggested in my previous post), as that would not be a good thing if two or more people used the database. Sorry about not being clear on that point.

Reply | Quote

ok! for help tks.
I have read your suggestion and the solution … “to write out the entire table when the Excel user closed the workbook…” is a good idea. For example insert a button on a sheet and save the data present in the sheet to save all.
If you haev a little time for me write an example macro in this post
Tks.
Salvatore

Reply | Quote

Hi Sal,
What you suggest is possible – it would involve using Automation with Excel reaching into Access and using the Access object model. In your situation, it would require some fairly complex code in Excel to detect that a cell in the worksheet had been modified, and then writing the change back to Access. A different approach would be to write out the entire table when the Excel user closed the workbook, but that seems to be pretty drastic, and you will encounter locking problems if two users are working at the same time. Another option might be to actually store the two tables in Excel, and attach the worksheets as tables in Access, but that effectively makes the database a single-user database, since two people cannot edit an Excel workbook at the same time.

Is there a reason not to use Access exclusively? (Most users not having Access installed would be a good one.) I wish I had a better suggestion – perhaps one of the other loungers will have an idea.

Reply | Quote