![]()
the connection string instruction, that becomes.In order to connect to a Microsoft Access db (or any other db) we can use the very same routine above, we must simply change: Only in rare occasions you will need to send SQL instructions in the specific dialect of a particular db. From VBA you can use the Microsoft Access SQL “dialect” against any database, it will be automatically translated by the driver. If you google “Microsoft Access SQL” you will find plenty of resources to learn something about SQL (for basic use, is an extremely simple language), here’s an introductory one in one page, and here’s another more comprehensive. Rst.Open “SELECT ProductID, ROUND(SUM(UnitPrice * Quantity)) AS Total FROM GROUP BY ProductID ORDER BY ROUND(SUM(UnitPrice * Quantity)) DESC ”, cnnĪs you can see, it sums UnitPrice * Quantity by ProductID from the Order Details table. Try this one instead of the previous one: Rst.Open “SELECT CustomerID, CompanyName FROM WHERE Country = ‘USA’ ORDER BY CompanyName ”, cnn If you do know some SQL, you can use a SQL instruction instead of the table’s name: instead of – rst.Open “”, cnn – you could write, for example: You can also put a button into the Excel sheet to run the procedure: pick a shape from Insert > Shapes > right-click on the shape > Assign Macro … > Double click on the macro’s name > click off then on the button to run the procedure. MICROSOFT ACTIVEX DATA OBJECT 6.0 LIBRARY CODENow, clic into the code and press F5 (or menu Run > Run Sub …), then go to the Excel Window to see the data pulled from the db. 'Copy the data into Excel, starting from the cell A10 Range("A9").Cells(1, iCols 1).Font.Bold = True Range("A9").Cells(1, iCols 1).Value = rst.Fields(iCols).Name 'Fetch the header of the "Categories" table and put it in Excel, starting from A9 'Notice that the sheet name must be followed by $ and included in square bracketsĬnn.ConnectionString = "Provider=.12.0 Data Source=C:\Test\db.xlsx Extended Properties='Excel 12.0 Xml HDR=YES' " ![]() 'Pass the connection string, open the connection, fetch the data from the sheet "Categories" into the recordset (rst)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |