Minimum Requirements for MS Access File I/O?

Get help with using AutoHotkey and its commands and hotkeys
TXShooter
Posts: 150
Joined: 13 Dec 2017, 09:27

Minimum Requirements for MS Access File I/O?

12 Oct 2019, 12:01

I'm delving into writing a script to work with an existing MS Access '98 Database, password protected, and is constantly in use by the system servers on a closed LAN that will never see the internet. This database has something like 70 tables with thousands and thousands of records. I won't be needing all of the tables for what I have in mind; just a few specific ones. This script will need to add and modify records, and quite possibly work with a new secondary database if this goes the way I think it will need to go. I'm not allowed to create new tables in the existing database. I also won't be allowed to create queries in the existing database. All that I will be allowed is to add and modify records in the existing database. The secondary database is a free-for-all... I can do whatever, but it won't be a duplicate of the primary database.

I've never written anything like this before, but have built some COM scripts to work with Excel.

In looking through the forums this morning, I found similar code to this:

Code: Select all

conn := ComObjCreate("ADODB.Connection")
conn.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=test.mdb;")
rs := conn.Execute("SELECT * FROM TestTable")
What is DBQ here?

Does AHK natively come with all that is necessary to work with a .mdb file?

What am I going to need (besides the AHK installer) in order to write this script to operate on a computer that is not one of the system servers, and doesn't have MS Access or anything else on it... it's just a bare-bones Windows 10 setup?
ostius
Posts: 1
Joined: 22 Mar 2019, 10:14

Re: Minimum Requirements for MS Access File I/O?

12 Oct 2019, 22:19

I use a slightly modified version of VxE's ADOSQL to query and modify Access databases. My version is ADOSQL2DF and was modified from VxE's to return a single recordset instead of multiple. Note that ADOSQL only works if you use 32 bit AHK and not 64 bit. A sample of how to use:

Code: Select all

#include ADOSQL2DF.ahk
connection_string := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\DatabaseName.mdb"
SQL_statement =
(
SELECT Count(LabelPrinted) AS CountOfLabelPrinted
FROM Merchandise
WHERE (PCName = 'PCWinName') AND (LabelPrinted = No)
)
Result := ADOSQL(connection_string,SQL_statement)
;Result is a 2D array with the first row of field names and results in row 2
if (ADOSQL_LastError) <> ""
{
 MsgBox %ADOSQL_LastError% 
 msgbox %ADOSQL_LastQuery%
 MsgBox % ErrorLevel
}
And the include file ADOSQL2DF:

Code: Select all

/*
###############################################################################################################
######                                      ADOSQL v5.04L - By [VxE]                                     ######
###############################################################################################################

Update by ostius to simply return a 2D array using 2 array keys
No option for more than 1 recordset. Removed text out option.

#########################################################
##  Requires AHK 32 bit!!!  Will not work with 64 bit! ##
#########################################################

	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.

	For AHK-L (v1.1 or later).
	Freely available @ http://www.autohotkey.com/community/viewtopic.php?p=558323#p558323

	IMPORTANT! Before you can use this library, you must have access to a database AND know the connection
	string to connect to your database.

	Varieties of databases will have different connection string formats, and different drivers (providers).
	Use the mighty internet to discover the connection string format and driver for your type of database.

	Examples:
	connection string for SQLServer (2005) listening on port 1234 and with a static IP:
	DRIVER={SQL SERVER};SERVER=192.168.0.12,1234\SQLEXPRESS;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
	
	For MS Access:
	connection_string := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\myfile.mdb"
To use:	
	SQL_statement =
(
SELECT Sum(TrxAmount) AS SumOfTrxAmount
FROM Activity
WHERE CustomerID='1988'
)
Result := ADOSQL(connection_string,SQL_statement)

*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. The object is a 2-dimensional array containing the
; result (the first row contains the column names). 
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.

	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.

	ComObjError( 0 ) ; We'll manage COM errors manually.

; Create a connection object. > http www.w3schools.com /ado/ado_ref_connection.asp  Broken Link for safety
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."

	oCon.ConnectionTimeout := 3 ; Allow 3 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 5 ; 5 seconds timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.

; Execute the query statement and get the recordset. > http www.w3schools.com /ado/ado_ref_recordset.asp  Broken Link for safety
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )

	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
			{
				o2DA := [] ; This is a 2-dimensional array.
				oFld := oRec.Fields
				o2DA.Insert( oRow := [] ) 
				Loop % cols := oFld.Count ; Put the column names in the first row.
					{
						oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name
						;msgbox % oRow[A_Index] ;to see data
					}
				While !oRec.EOF ; While the record pointer is not at the end of the recordset...
				{
					o2DA.Insert( oRow := [] )
					oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
					Loop % cols
					{
						oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value
						;msgbox % oRow[A_Index] ;to see data
					}
					oRec.MoveNext() ; move the record pointer to the next row of values
				}
	}
	Else ; Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http www.w3schools.com /ado/ado_ref_error.asp  Broken Link for safety
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}
; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o2DA
	
} ; END - ADOSQL( Connection_String, Query_Statement )
TXShooter
Posts: 150
Joined: 13 Dec 2017, 09:27

Re: Minimum Requirements for MS Access File I/O?

14 Oct 2019, 09:54

ostius wrote:
12 Oct 2019, 22:19
I use a slightly modified version of VxE's ADOSQL to query and modify Access databases. My version is ADOSQL2DF and was modified from VxE's to return a single recordset instead of multiple. Note that ADOSQL only works if you use 32 bit AHK and not 64 bit. A sample of how to use:
Thank you, but, please keep in mind what I asked...
What am I going to need (besides the AHK installer) in order to write this script to operate on a computer that is not one of the system servers, and doesn't have MS Access or anything else on it... it's just a bare-bones Windows 10 setup?
As I read your test script, and if I'm not mistaking, ADOSQL2DF requires some form of MS Access because of connection_string := "Provider=Microsoft.Jet.OLEDB.4.0;...". Is that correct? The computer that this will be on will not be capable of having the paid version of MS Access on it. Will it still work? And if not, will installing the 2010 Redistributable work?

Return to “Ask For Help”

Who is online

Users browsing this forum: asad41163, BreezeB, malcev, MannyKSoSo, RogerWilcoNL, Skugg, Zudegia, zvit and 204 guests