MS ACCESS 2003触发器（Query Event）和Excel导入 [英] MS ACCESS 2003 triggers (Query Event), and Excel import
问题描述
数据库由超过40个表组成，冗余，无键，和无指标;换句话说，这是一团糟。经过很多麻烦，我能够改进设计并减少桌子的数量。
然而，我发现自己面临着很多挑战。大多数这些挑战可以通过触发器来克服，但是在阅读了不同论坛中的许多答案后，我才明白，它们在Access 2003中不存在，应该被替换为链接到表单的查询。这种解决方案的一个问题是它需要一个窗体和一个按钮。所以我想，当使用表单从excel文件导入数据时，我会实现这样的查询。由于表不完全遵循excel文件的格式，因此我需要指导，以了解如何将特定Excel列从特定表格导入到Db中的表的相应列。
另外，这是更具挑战性的地方（至少对我来说），我有不同的表不直接连接（因为例如桥表），但我仍然需要保持一定的完整性在所有表的数据之间。
如果我们有表A，B，C;表A连接到表B连接到表C：
我需要在表A中插入行时插入或删除表C的行。另外，我需要一些表C的列（在价值方面）与表A的列。我知道这可能看起来像是设计中的一个问题，但我向你保证不是。对于没有提供表格的细节，我很抱歉，但我签署了不公开条款。
最后，这可能看起来像是一个愚蠢的问题，但是在表设计中找不到一个地方可以在某些方面强加一些数学验证规则列取决于其他列。我发现我们可以在单个列上有验证规则（例如Is Null Or> = 0），但是对于某些列，它们的值应该依赖于其他列。例如，列B应等于1.2 *列A（有时这些列位于同一个表中，但并不总是）。
感谢您的帮助。我非常感谢任何可以为我面临的任何问题提供任何帮助;我会保留你所需要的任何额外的信息。
我同意user2174085，你的问题很多。
我的方法是在类中建模数据并使用这些来写入数据库。这将允许您根据需要验证您的数据，并根据需要将数据导入尽可能多的表。
此方法是CRUD应用程序的标准。
以下是一个简单的示例，我希望）清楚地描述）：
1）定义我们的示例数据库：
表1：客户端（ID，名称，地址ID）
表2：地址（ID，街道）
strong> 2）定义我们的示例电子表格：
客户端：
ID，Name，AddressID
1，Bob，1
2，Jim，2
地址：
ID，Street
1，Gold Street
2，Derp Street
3）定义我们的两个类，这样我们可以建立单个客户端和一个地址。
要在MS Access中使用这些，请在代码编辑器窗口中添加一个类并粘贴以下类。有关更多信息，这是一个很好的vba类资源： http://www.cpearson.com/exc el / classes.aspx
客户端类：
选项比较数据库
选项显式
Dim intID As Integer
Dim strName As String
Dim intAddressID As Integer
'here we define在数据库（usp_Clients_InsertRecord）中的查询（QueryDef），从类变量中填充其参数并执行它来写入记录
Public Sub Insert（）
Dim qdfTemp As QueryDef
Set qdfTemp = CurrentDb（）。QueryDefs（usp_Clients_InsertRecord）
带有qdfTemp
.Parameters（pName）= strName
.Parameters（pAddressID）= pAddressID
结束With
qdfTemp.Execute
End Sub
地址类：
选项比较数据库
选项显式
Dim intID As Integer
Dim strStreet As String
'这里我们定义一个查询（QueryDef），它位于数据库（usp_Addresses_InsertRecord）中，popul从类变量中执行它的参数并执行它写入记录
Public Sub Insert（）
Dim qdfTemp As QueryDef
设置qdfTemp = CurrentDb（）。QueryDefs（usp_Addresses_InsertRecord）
使用qdfTemp
.Parameters（pStreet）= strStreet
结束
qdfTemp.Execute
End Sub
您可以在此处执行验证，例如创建一个客户端记录，然后调用一个验证方法，告诉您数据是否正常。
ID未写入的原因是因为它是数据库中的自动编号。它仍然包含在类中，因为我们可能需要从数据库中的数据创建客户端/地址记录，并使用其信息来写入另一条记录。例如，要编写客户端记录，我们可能需要检索一个地址记录，并填写用于写客户端记录的AddressID。
4）上述类使用查询（存储过程）写入数据库，以下是一个示例查询：
usp_Clients_InsertRecord
参数pName文本（255），pAddressID长;
插入客户端（Name，AddressID）
值（pName，pAddressID）;
5）好，好，但是我们如何从excel中获取数据，进入类，将其写入数据库？为此，我们使用管理类，这些类包含负载的客户端或地址记录，从电子表格加载并存储在集合中。该集合被循环，调用Insert方法将记录写入数据库。
这提供了一个如何将数据写入表A的问题的答案，然后B然后C.您将创建3个类，使用数据填充它们，然后将A写入数据库，然后使用最后写入记录的ID将表B写入表A（使用查询检索）等等。
这是一个管理类的例子：
Option Explicit
'我们的客户端集合
私有mcolClients作为集合
'向客户端集合添加对象
公共函数AddByParameter（Byval名称作为字符串，byval AddressID为整数）
dim objClient as Client
Set objClient = New Client
with objClient
.strName = Name
.intAddressID = AddressID
end with
mcolClients.Add objClient，Name
结束函数
'构造函数
Private Sub Class_Initialize（）
设置mcolClients = New Collection
End Sub
'你需要这样才能遍历一个集合
公共函数NewEnum（）作为IUnknown
设置NewEnum = mcolImportQuestions [_ NewEnum]
结束函数
'然后你可以遍历这个集合，在每个记录上调用insert方法：
public Sub InsertAllClients
dim objClient as Client
为mcolClients中的每个objClient
objClient.Insert
next objClient
end function
我认为我已经走了一会儿，如果这很有用，请让我知道/提出问题，我可以把它弄清楚一些：）
I am working on a project in a company, and because of such i am bound to use MS ACCESS 2003 operating on XP (although, i don't think the OS is relevant here). Because the company is using other application (and also external sources), the data that is used as input in the table is not always sanitized. However, i noticed we can always get the data in .XLS (excel) format.
The Database was composed of more than 40 tables, with redundancies, no keys, and no indexes; in other word it was a mess. After a lot of troubles I was able to improve the design and reduce the number of tables.
Yet, I found myself facing few challenges. Most of these challenges could be overcome with triggers, but after reading many answers in different forums I came to understand that they do not exist in access 2003 and should be replaced with queries linked to the forms. One of the problem with such a solution is that it needs a form and a button. So I thought, I would implement such queries when importing the data from the excel files using a form to do so. Since the tables are not exactly following the format of the excel files, I need directions to learn how to import specific excel columns from specific sheets in changing directories to the corresponding columns of the tables in the Db.
Also, and this is where it gets more challenging (at least for me), I have different tables that are not directly connected (because of for example bridge tables), yet I still need to keep some integrity between the data of all tables. If we have table A, B, C; table A being connected to table B that is connected to table C: I need the Rows of table C to be inserted or deleted upon insertion of rows in table A. Also, I need some columns of table C to be consistent (in term of values) with columns of table A. I know this might seem at first like a problem in design, but I assure you it is not. I am sorry for not giving details of the tables but I signed a non-disclosure clause.
Finally, and this might seem like a "stupid" question, but I can’t find in access a place in the table design to impose some mathematical validation rules on some columns depending on other columns. I found that we can have validation rules on a single column (for example Is Null Or >= 0), but for some columns their values should depend on other columns. For example, column B should equal 1.2*column A(sometimes these columns are in the same table, but not always).
Thank you for your help. I am much grateful for any assistance you can provide in any of the problems I am facing; and I stay at your disposition for any additional piece of information you might need.
I agree with user2174085, there is quite a lot in your question.
My approach would be to model your data in classes and use these to write to your database. This would allow you to validate your data as you require, and to import the data into as many tables as you require.
This approach is standard in CRUD applications.
Here is a simple example for illustration purposes with the steps (I hope) clearly described):
1) Define our example database:
Table1: Clients (ID, Name, AddressID)
Table2: Addresses (ID, Street)
2) Define our example spreadsheets:
Clients: ID, Name, AddressID 1, Bob, 1 2, Jim, 2
Addresses: ID, Street 1, Gold Street 2, Derp Street
3) Define our 2 classes which allow us to model a single Client and a single Address.
To use these in MS Access, add a class in the code editor window and paste the below classes in. For more information this is a good vba classes resource:http://www.cpearson.com/excel/classes.aspx
Client class:
Option Compare Database
Option Explicit
Dim intID As Integer
Dim strName As String
Dim intAddressID As Integer
'here we define a query (QueryDef) which is in the database (usp_Clients_InsertRecord), populate its parameters from the class variables and execute it to write the record
Public Sub Insert()
Dim qdfTemp As QueryDef
Set qdfTemp = CurrentDb().QueryDefs("usp_Clients_InsertRecord")
With qdfTemp
.Parameters("pName") = strName
.Parameters("pAddressID") = pAddressID
End With
qdfTemp.Execute
End Sub
Address class:
Option Compare Database
Option Explicit
Dim intID As Integer
Dim strStreet As String
'here we define a query (QueryDef) which is in the database (usp_Addresses_InsertRecord), populate its parameters from the class variables and execute it to write the record
Public Sub Insert()
Dim qdfTemp As QueryDef
Set qdfTemp = CurrentDb().QueryDefs("usp_Addresses_InsertRecord")
With qdfTemp
.Parameters("pStreet") = strStreet
End With
qdfTemp.Execute
End Sub
You can perform validation at this point, you could for example create a client record, then call a validate method that tells you if the data is ok.
The reason the ID is not written is because it is an autonumber in the database. It is still included in the class because we may need to create a client / address record from data in the database, and use its information to write another record. For example, to write a client record, we may need to retrieve an address record, complete with AddressID that is used in writing the client record.
4) The above classes use queries (stored procedures) to write to the database, here is an example query:
usp_Clients_InsertRecord
PARAMETERS pName Text (255), pAddressID Long; Insert into Clients(Name, AddressID) Values (pName, pAddressID);
5) This is all well and good, but how do we get our data from excel, into the class, to write it to the database? To do that we use managing classes, these are classes that contain a load of Client or Address records, loaded from the spreadsheet and stored in a collection. This collection is looped over, calling the Insert method to write the records to the database.
This provides an answer to your question of how to write data to tables A, then B then C. You would create 3 classes, populate them with data, then write A to the database, then write B using the ID of the last written record to table A (retrieved using a query) and so on.
Here is an example of a managing class:
Option Explicit
'our clients collection
Private mcolClients As Collection
'adding objects to the clients collection
Public Function AddByParameter(byval Name as string, byval AddressID as integer)
dim objClient as Client
Set objClient = New Client
with objClient
.strName = Name
.intAddressID = AddressID
end with
mcolClients.Add objClient, Name
end function
'constructor
Private Sub Class_Initialize()
Set mcolClients = New Collection
End Sub
'you need this to be able to iterate over a collection
Public Function NewEnum() As IUnknown
Set NewEnum = mcolImportQuestions.[_NewEnum]
End Function
'you can then iterate over the collection, calling the insert method on each record:
public Sub InsertAllClients
dim objClient as Client
for each objClient in mcolClients
objClient.Insert
next objClient
end function
I think I've gone a bit, if this is useful please let me know / ask questions and I can flesh it out a bit :)
这篇关于MS ACCESS 2003触发器（Query Event）和Excel导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
