Updating a table from Excel
-
Our app gets info from a product database (MDB), and we have a database editor tool included in our package. Our thought there was to provide users with a way to maintain their DBs without having to have Office installed. So far, so fine-by-us. However, some of our users want to be able to update their DBs from data in Excel (though if they have Excel, they should have access to Access no? And they could just do this directly? Anyway. Whatever.) So, for the very small percentage of our users who are interested, I've added a Query window to our database editor app. It allows users to run SELECT, DELETE, UPDATE and INSERT queries (Yes I know the risks! Yes, some operations are transactionally protected!). Behind my query window / data-bound grid is a
CDaoRecordset(yes, I know it's old, and deprecated, but it works, and extricating DAO from the app is a lot more work than I have time for at the moment). So the question is, can I write a query in my query window that can update / insert to my component table from an Excel source? I'm thinking something along the lines of:SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])But executing something like that, the app complains about an unrecognised FROM clause. Any thoughts / suggestions are much appreciated.
-
Our app gets info from a product database (MDB), and we have a database editor tool included in our package. Our thought there was to provide users with a way to maintain their DBs without having to have Office installed. So far, so fine-by-us. However, some of our users want to be able to update their DBs from data in Excel (though if they have Excel, they should have access to Access no? And they could just do this directly? Anyway. Whatever.) So, for the very small percentage of our users who are interested, I've added a Query window to our database editor app. It allows users to run SELECT, DELETE, UPDATE and INSERT queries (Yes I know the risks! Yes, some operations are transactionally protected!). Behind my query window / data-bound grid is a
CDaoRecordset(yes, I know it's old, and deprecated, but it works, and extricating DAO from the app is a lot more work than I have time for at the moment). So the question is, can I write a query in my query window that can update / insert to my component table from an Excel source? I'm thinking something along the lines of:SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])But executing something like that, the app complains about an unrecognised FROM clause. Any thoughts / suggestions are much appreciated.
Hi, It's been years since I last used DAO but few things come in mind: - The statement executed using CDaoRecordSet is (if I recall correctly) parsed by the provider. This actually means that your DAO provider should be able to understand the SQL syntax you use, but that's not possible (since you're using T-SQL syntax) - Unfortunately CDaoRecordSet doesn't have the capability to pass the statement to the server as-it-is - You could try to use CDaoQueryDef instead and use the passthrough option so that the provider won't interfere. After all you're not trying to get any results back to the client but to populate a table in the database Also keep in mind that in your statement you're referring to a file which resides on the database server. Meaning that the file xltest.xls must reside in directory C:\test at the database server.
The need to optimize rises from a bad design.My articles[^]