VBA从Excel导入到Access现有表的末尾 [英] VBA to Import from Excel to end of Access existing table
问题描述
我有一个包含24个不同列的现有database.mdb表。我需要能够从最后一条记录之后的Excel文件中将1条记录和最多500条记录导入此表。我找不到有助于找到最后一条记录的VBA。这需要完全自动化。这是Users.xls和Users.mdb的格式
UserID ChsetIdx FirstName LastName MiddleName Street City Zip State
HomePhone Workphone LastEventLog ExpirationDate NeverExpiers活动已删除
GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1
CustomType2 CustomType3 CustomType4
第一件事是从用户选择的目录中选择Users.xls并将其导入到正确的Users.mdb（不同目录中有多个）注意：没有什么可以修改为更新的格式Users.accdb，因为它也在被读取和写入通过遗留程序。
Users.mdb中的UserID是自动编号的，目前可能以30和最多650结尾，具体取决于所选的users.mdb。所以我需要开始导入原始数据库结束的位置。
有人可以帮忙吗？
谢谢
I have an Existing database.mdb table with 24 different columns. I need to be able to Import 1 record and up to 500 records to this table from Excel file starting after the last record. I can''t find a VBA that will help with finding the last record. This will need to be fully automated. This is the format of both Users.xls and Users.mdb
UserID ChsetIdx FirstName LastName MiddleName Street City Zip State
HomePhone Workphone LastEventLog ExpirationDate NeverExpiers Active Deleted
GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1
CustomType2 CustomType3 CustomType4
The first thing is to select Users.xls from a directory the user selects and import it to the proper Users.mdb (there are multiple in different directories) Note: Nothing can be modified to the newer format Users.accdb because it is also being read and written to by a legacy program.
UserID in Users.mdb is autonumbered and currently may end in 30 and up to 650 depending on the users.mdb selected. So I need to start importing where the original database ends.
Can someone please help with this?
Thank you
推荐答案
在Access中没有最后一条记录这样的东西，因为所有记录都可以在不同的方式，这意味着''最后一条记录''总是取决于订购。
如果一个字段是自动编号的，你永远不应该更新这个字段。当创建新记录时，它将''自动''获得最后使用的值+1。
对于''+1''部分：''增量''必须当然，设置为1 ......
There is no such thing as a ''last record'' in Access, because all records can be ordered in different ways, which means the ''last record'' always depends on the ordering.
If a field is autonumbered than you should never update this field. When a new record is created it will ''autmatically'' get the last used value +1.
For the ''+1'' part: The ''Increment'' must be set to 1, of course....
Luuk，感谢您的回复！我从Access手动导入我的数据，将自动编号字段留空，它工作得很好。我只需要一些指导就可以从Access或Excel运行VBA，这样就可以自动完成。再次感谢你！
Luuk, Thank you for responding so quickly! I am importing my data manually from Access leaving the Autonumber field blank, It is working great. I just need some guidance to get a VBA to run from either Access or Excel so this can be done automatically. Thank you Again!
Asking4Help，
欢迎来到Bytes！
你为什么要保留自动编号字段空白？这违背了拥有Autonumber领域的目的。
正如Luuk正确地描述的那样，最后？数据库中的记录只是输入数据库的最后一个记录。但是，由于表中的排序，它的位置可以改变。但是，如果您有自动编号字段，请使用IT，然后，您输入的最后一条记录将始终具有自动编号字段中的最高值。您仍然可以根据需要对表格进行排序和排序。
也许我们都遗漏了您的描述中无法解释的内容？
Asking4Help,
Welcome to Bytes!
Why would you keep the Autonumber field blank? That defeats the purpose of having an Autonumber field.
As Luuk rightly describes, the ?last? record in a database is simply the last one entered into the DB. But, because of ordering in the Table, it?s location can change. However, if you have an Autonumber field, USE IT, and then, the last record you enter will always have the highest value in the Autonumber field. You can still sort and order the table however you want.
Perhaps we are both missing something that isn?t clear from your description?
这篇关于VBA从Excel导入到Access现有表的末尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
