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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Update Access records from Excel
- Thread starter 2077delta
- Start date
Russell Hauf
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 1,611
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
HTH,
Russell
Upvote
0
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
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.
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.
Code:
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 SubYou 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.
Upvote
0
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
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
Upvote
0
Similar threads
- Solved
- Question
