Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Query MS Access from Workbook (Read 3454 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Query MS Access from Workbook
18.09.06 at 20:44:21
Query MS Access from Workbook
John from the USA asked the following question.
I have started to use SQLXL and I like it. Can you propose a solution for the following:
I have an excel workbook with an Access database. One of the sheets on the workbook is "Data Entry". Here I would like to enter one key field, press a button and have 8 other cells uptate from the access database. VBA macro is preferred so I can replicate to similiar sheets and change the DB to MSSQL for the real installation.
You can use a few approaches to do this. In order of my preference:

  • Use the SQLQueryValue function.
    In each of the 8 cells where the value from the MS Access or MSSQL database should go, enter a SQLQueryValue formula. You can create a template from the Worksheet functions menu in the SQL*XL menu.
    Use the Excel string concatenation function to build up the statement. Example:
    =SQLQueryValue( concatenate("select count(*) from mytable where id = ", D12) )
    The only thing you need to do is connect to the database just as normal. If you want you can automate the connecting to the database at startup of the workbook or a simpeler solution, through a button on the worksheet.
  • Query the database and use Excel formulae to put the correct value in your 8 cells.
    You could execute a query to fetch the data you wish to show. You can record a macro to connect to the database and retrieve this information somewhere where the user cannot see it. Set Application.ScreenUpdating = False to not let the window refresh. Then use the Excel worksheet formulas to point to the cell where you know the result will be put.  
    For example if you want to show the third result from the query and you dumped the results of the query in Z1 (first row are the titles) you can use the simple Excel forumal =Z4 to refer to the 3rd data row.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1