andy7789
asked on
VBA Access ADO: how to optimize creating a temp table
Hi X-perts,
I have a long list (sometimes over a few thousand items) and need to create a temp table for further SQL queries (joining it with other tables).
The way how I am doing this is straightforward: I create a new table and loop throughout all list items one-by-one adding records to the table
Is there a way of doing it better? Dumping it to the table somehow rather than looping one-by-one
Thanks
I have a long list (sometimes over a few thousand items) and need to create a temp table for further SQL queries (joining it with other tables).
The way how I am doing this is straightforward: I create a new table and loop throughout all list items one-by-one adding records to the table
Is there a way of doing it better? Dumping it to the table somehow rather than looping one-by-one
Thanks
ASKER
The list is a listbox on a userform, it contains just string names.
The temp table that i am building has just two fields: autoindex and [name], which corresponds to list names.
The temp table that i am building has just two fields: autoindex and [name], which corresponds to list names.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all. Just to make sure that I have explained the problem correctly:
1) The temp table should be created from a userform list. This list could be generated and altered manually by the user. That's why i cannot use a query that generated a list, because there is no such a query.
2) The only object we have in hands is a list itself
3) I need that temp table to build other queries joining fields withe these generated from a list. I used to have another solution by building a string with all the items from the list and adding it to a query, i.e.
....AND [myField IN (item1, item2.....itemN). It works fine for relatively short lists, but creates a disaster for lists of 2,000 - 3,000 items.
4) this is the reason why I am using the temp table.
5) I am using MS Access ADO.... I am not sure if i can use OPENXML here
1) The temp table should be created from a userform list. This list could be generated and altered manually by the user. That's why i cannot use a query that generated a list, because there is no such a query.
2) The only object we have in hands is a list itself
3) I need that temp table to build other queries joining fields withe these generated from a list. I used to have another solution by building a string with all the items from the list and adding it to a query, i.e.
....AND [myField IN (item1, item2.....itemN). It works fine for relatively short lists, but creates a disaster for lists of 2,000 - 3,000 items.
4) this is the reason why I am using the temp table.
5) I am using MS Access ADO.... I am not sure if i can use OPENXML here
>> I am using MS Access ADO.... I am not sure if i can use OPENXML here<<
No you cannot. In future, do not include inappropriate zones such as MS SQL Server.
No you cannot. In future, do not include inappropriate zones such as MS SQL Server.
Are there easy selection rules for the items you want?
Kelvin