Update Access records from Excel

[2077delta]

Is there a way to update records in an Access table from Excel. What I'm looking for is a way to let a user update an Acess table when they don't have Access on their machine. We have limited resources and only a couple copies of Access but everyone has Excel. With as much as MS touts the interoperability of its applications, this seems like it should be doable. If possible can I copy an Access user input form to Excel and go from there? I had some mention setting up Access as an object in Excel, but wouldn't that still require the user to have Access on their desktop? If not, what are my available options i.e. MS Query, Excel user form, other or NONE? I can find plenty of info on data flow from Access to Excel but little of none going the other way.

 

[Russell Hauf]

You can link an Excel worksheet to an Access database and let users update the worksheet. If you really want a form, you can design a UserForm in Excel to allow the user to enter data that way (or you may want to try going to Data-Form).

HTH,

Russell

 

[rbsmit02]

If you really want a form, you can design a UserForm in Excel to allow the user to enter data that way (or you may want to try going to Data-Form).

I have set it up so my users can add records to an access database and return them to a sheet.. But I cannot figure out how to update the records.


Any hints?

 

[Corticus]

The following example uses DAO to append from an Excel sheet to an Access table, Access is never opened and not needed on the PC. Make sure to turn on the DAO 3.6 reference from Tools|References in te VB editor.

Sub appProb()

    On Error GoTo 1
    
    Dim XLTable As TableDef

    Set Db = OpenDatabase(mdbFile)
    Set XLTable = Db.CreateTableDef("tblProbTemp")
    
    XLTable.Connect = "Excel 5.0;DATABASE=" & xlsFile
    XLTable.SourceTableName = "tblXLProb"
    Db.TableDefs.Append XLTable

    strSQL = "Insert into tblProb Select * FROM tblProbTemp"
    Db.Execute strSQL

1:
    Err.Clear
    On Error Resume Next
    Db.TableDefs.Delete "tblProbTemp"
    Db.Close

End Sub

You must define your Excel table as a named range, and it should be in the same format as the table that will receive the data. In my example it is tblXLProb. Mdbfile is the location of the db.
What this code does is, append a temp table from your Excel sheet's named range specified (remember a named range that's properly formatted in Excel is considered a table) to an Access db of your specifiaction. In my example, the new temp table in the db is named 'tblProbTemp'. You then can perform any SQL expression using the temp table as your source. My example just takes every record from the temp table and appends it to tblProb. You can use any SQL, though. Finally, this temp table is deleted from Access.

 

[rbsmit02]

how about directly from a userform?

 

[Corticus]

Have the dataform in excel look to a sheet. This sheet will be used for updating your Access tables.

 

[irisbu]

I have an excel template. When I CLOSE the workbook - I want to update an access table with the excel properties.

I have a code theat Reads from the access table - (with msgbox)...
BUT when I try to add a new recoedset or update an existing recordset - It does not do it!

Since It's almost the same question - maby someone can help me with that?

here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim dbDatabase As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim a As Integer
Dim b As Integer
Dim flag As Boolean
flag = True

dbDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\masterfood.mdb"

rs.Open "select * from categories order by [mispar];", dbDatabase, adOpenStatic

With rs!mispar
Do

If ActiveWorkbook.CustomDocumentProperties("mispar mi") <> rs!mispar Then
flag = False
MsgBox "no muach" & " " & rs!mispar & " " & flag
Else: flag = True
End If

rs.MoveNext
Loop Until rs.EOF
End With
If flag = False Then
rs.MoveLast
MsgBox rs!mispar & "last"

rs.AddNew
rs!irgun = rs!irgun = ActiveWorkbook.CustomDocumentProperties("irgun")
MsgBox rs!irgun & " " & ActiveWorkbook.CustomDocumentProperties("irgun")
rs.Update
End If