用Excel数据和可能的宏更新Access数据库中的2个字段 [英] update 2 fields in Access database with Excel data and probably a Macro
问题描述
在主数据库中，我有一个小表，其中包含两个日期和ID号.我使用此表来更新查询并进行一些分析.该表如下所示:
Number | Date
1 | 09.07.2012.
2 | 10.07.2012.
我想做的事情是拥有一个excel文件，该文件在启动后会弹出一个表单.
该表格应包含2个字段和2个按钮.在这些字段中，我输入2个日期(使用日期选择器或其他工具)，并使用第一个按钮使用新数据更新Access中提到的表(删除旧日期并使用新日期更新)，使用另一个我在该Access数据库中启动预定义的宏
这有多复杂?你能指导我解决吗?一些示例代码会很好.
Command25_Click()
CurrentDb.Execute "DELETE * FROM Datumi"
Dim tbl As Recordset
Set tbl = CurrentDb.OpenRecordset("Datumi")
tbl.AddNew tbl!brojka = "1"
tbl!datum = Text8.Value
tbl.Update
tbl.AddNew tbl!brojka = "2"
tbl!datum = Text10.Value
tbl.Update
这要走很长一段路，因为我正在使用日期更新来演示如何还可以运行保存的查询.
'SQL for stored query, this assumes you
'will be updating based on some key
UPDATE DatesTable t
SET t.Date1 = [@Date1], t.Date2 = [@Date2]
WHERE t.aAuto=[@Key]
'Code for Excel
'You could set a library reference to
'Microsoft ActiveX Data Objects x.x Library
Dim cmd As Object
Dim cn As Object
Set cmd = CreateObject("ADODB.Command")
Set cn = CreateObject("ADODB.Connection")
'Connection string, see also http://connectionstrings.com
strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=z:\docs\test.accdb"
cn.Open strCon
'Name of the saved query
cmd.CommandText = "UpdateDates"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
'Some parameters.
'http://www.w3schools.com/ADO/met_comm_createparameter.asp
'Make sure you get the type right, you will find details here:
'http://www.w3schools.com/ADO/ado_datatypes.asp
'You will find direction here:
'http://www.w3schools.com/ado/prop_para_direction.asp
'Make sure you get the order right
'adDate = 7, adInteger = 3, adParamInput = 1
cmd.Parameters.Append cmd.CreateParameter("@Date1", 7, 1, , txtDate1)
cmd.Parameters.Append cmd.CreateParameter("@Date2", 7, 1, , txtDate2)
cmd.Parameters.Append cmd.CreateParameter("@Date2", 3, 1, , MyUniqueKey)
'recs : return for records affected
'adExecuteNoRecords = 128 : no records are returned by this query,
'so this increases efficiency
'http://www.w3schools.com/ADO/ado_ref_command.asp
cmd.Execute recs,,128
'Did it work?
MsgBox "Records updated: " & recs
In my master database I have a small table that contains two dates and ID numbers. I use this table for updating queries and do some analysis. The table looks like this:
Number | Date
1 | 09.07.2012.
2 | 10.07.2012.
The thing I would like to do is to have an excel file that pops-up a form after startup.
That form should contain 2 fields and 2 buttons. In those fields I input 2 dates (with date picker or whatever) and with 1st button I update mentioned table in Access with fresh data (delete old dates and update with new ones) and with the other I start a predefined Macro in that Access database.
How complicated is this? can you guide me to the solution? Some sample code would be excellent.
Command25_Click()
CurrentDb.Execute "DELETE * FROM Datumi"
Dim tbl As Recordset
Set tbl = CurrentDb.OpenRecordset("Datumi")
tbl.AddNew tbl!brojka = "1"
tbl!datum = Text8.Value
tbl.Update
tbl.AddNew tbl!brojka = "2"
tbl!datum = Text10.Value
tbl.Update
This is going the long way about to a certain extend because I am using the update of the dates to demonstrate how you might also run the saved queries.
'SQL for stored query, this assumes you
'will be updating based on some key
UPDATE DatesTable t
SET t.Date1 = [@Date1], t.Date2 = [@Date2]
WHERE t.aAuto=[@Key]
'Code for Excel
'You could set a library reference to
'Microsoft ActiveX Data Objects x.x Library
Dim cmd As Object
Dim cn As Object
Set cmd = CreateObject("ADODB.Command")
Set cn = CreateObject("ADODB.Connection")
'Connection string, see also http://connectionstrings.com
strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=z:\docs\test.accdb"
cn.Open strCon
'Name of the saved query
cmd.CommandText = "UpdateDates"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
'Some parameters.
'http://www.w3schools.com/ADO/met_comm_createparameter.asp
'Make sure you get the type right, you will find details here:
'http://www.w3schools.com/ADO/ado_datatypes.asp
'You will find direction here:
'http://www.w3schools.com/ado/prop_para_direction.asp
'Make sure you get the order right
'adDate = 7, adInteger = 3, adParamInput = 1
cmd.Parameters.Append cmd.CreateParameter("@Date1", 7, 1, , txtDate1)
cmd.Parameters.Append cmd.CreateParameter("@Date2", 7, 1, , txtDate2)
cmd.Parameters.Append cmd.CreateParameter("@Date2", 3, 1, , MyUniqueKey)
'recs : return for records affected
'adExecuteNoRecords = 128 : no records are returned by this query,
'so this increases efficiency
'http://www.w3schools.com/ADO/ado_ref_command.asp
cmd.Execute recs,,128
'Did it work?
MsgBox "Records updated: " & recs
这篇关于用Excel数据和可能的宏更新Access数据库中的2个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
