Category: SQL
-
DSNless Connection in VBA to SQL Server Tables
When we released updated access database which were linked to an sql source the deployment requires re-connection to the client environment ; We would need to ensure the client PC had the DSN setup and tested, to simplify the process we call this DSN-less function. The program tests the connection when opening and advises the…
-
SQL Update Scripts
In the absence of a UI for a given table, updates to records on that table need to be executed as SQL Update Scripts. This process would usually involve some development resource to create the script under the instruction of a given user. The obvious solution would be to add a UI to allow the…
-
Running SQL Stored Procedures from Access VBA
Once the backend data was migrated to SQL from an Access accdb file we created a number of Stored Procedures to dramatically increase the processing speed.
-
Access UI Design
When working on multiple tables to create simple Access UI design forms the process to create each UI can become tiresome. To simplify the process I now take all the fields from the table and place them on the form in the desired position and then call the following code on open event. Access UI…
-
Importing Oracles ALL_TAB_COLUMNS into Access
If you’re working with Oracle databases and need to recreate tables in Microsoft Access for proof of concept or other tasks, Oracles ALL_TAB_COLUMNS view is an invaluable resource. This table provides detailed metadata about all tables, fields, and data types in a given Oracle instance. Here’s how I used it to streamline my process: Step…
-
Benefits of using Pass-through queries
We have recently converted a database from Access to SQL and needed to increase the speed of the reports when working with a large recordset. We amended this code from the web to a pass-through which significantly increased the speed on 2 reports but reduced the speed on another – so its a case of…
