Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

[AHK_L] ADO/MS Access Data Base Functions


  • Please log in to reply
6 replies to this topic
wooly_sammoth
  • Members
  • 644 posts
  • Last active: Jul 03 2013 08:08 PM
  • Joined: 12 May 2009
Edit2: OK let's get this done properly. Apologies for the delay, I've been busy becoming a dad for the first time.

Following on from my support request here.

These are some simple functions for writing data to, getting data from and getting error messages from an ADO / MS Access Database.

This is my first attempt at using the native COM within AHK_L so I'd appreciate any comments or suggestions regarding the functions.

The code for ADO_Read() borrows heavily from Frankies post on this page of the COM Object reference post

I hope someone finds these useful.

Edit3: Worth mentioning that I use ComObjError(0) when I use these functions

/*;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; 
; ADO_Write - For use with INSERT or UPDATE statements
;
; Inputs:
;	SQL - An SQL statement eg. 
;			INSERT INTO [table] (columns) VALUES (values);
;	sDbFile - Path to the database file
;
; Returns:
;	On Failure - ADO specific Error Text
;                         Also sets ErrorLevel to the error text
;	On Success - 0
*/
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

ADO_Write(SQL, sDbFile) {
	pcn := ComObjCreate("ADODB.Connection")
	pcn.ConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . sDbFile . ";"
	pcn.Open()
	If (pcn.State != 1)
	{
		ErrorLevel := "The connection to the database failed"
		Return ErrorLevel
	}
	pcn.Execute(SQL)
	pcn.Close()
	If (ErrorLevel := ADO_GetError(pcn))
	{
		Return ErrorLevel
	}
	Else
	{
		Return 0
	}	
}

/*;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; 
; ADO_Read - For use with SELECT statements
;
; Inputs:
;	SQL - An SQL statement eg. 
;			SELECT ALL * FROM [table];
;	sDbFile - Path to the database file
;	sNames - Set to 1 to return column names from the table
;
; Returns:
;	On Failure - ADO specific Error Text
;                         Also sets ErrorLevel to the error text
;	On Success - Returns data in the following format.
					column1|column2|column3|etc.`n
					column1|column2|column3|etc.`n
				 
				 If sNames is set to 1 an extra data line is 
				 added at the top containing the column names 
				 delimited with | and ending with a ^ 
				 
*/
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

ADO_Read(SQL, sDbFile, sNames=0) {
	pcn := ComObjCreate("ADODB.Connection")
	pcn.ConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . sDbFile . ";"
	pcn.Open()
	If (pcn.State != 1)
	{
		ErrorLevel := "The connection to the database failed"
		Return ErrorLevel
	}
	prs := pcn.Execute(SQL)
	If ADO_GetError(pcn)
	{
		ErrorLevel := ADO_GetError(pcn)
		Return ErrorLevel
	}
	If (prs.BOF) and (prs.EOF)
	{
		ErrorLevel := "The returned RecordSet contains no records"
		Return ErrorLevel
	}
	While !prs.EOF
	{
		prsFields := prs.Fields
		Names =
		Loop, % prsFields.Count ;%
		{
			prsField := prsFields.Item(A_Index-1)
			Data .= prsField.Value . "|"
			Names .= prsField.Name . "|"
		}
		StringTrimRight, Data, Data, 1
		Data .= "`n"
		prs.MoveNext()
	}
	pcn.Close()
	StringTrimRight, Names, Names, 1
    StringTrimRight, Data, Data, 1
	If (sNames)
	{
		Return (Names . "^" . Data)
	}
	Else
	{
		Return Data
	}
}

/*;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; 
; ADO_GetError - Returns the last error from the supplied ADO connection Objetc
;
; Inputs:
;	Conn - A current ADO Connection object
;	Text - Set to 0 to return the error number rather than the error text
;			(As far as I can tell this is the same as A_LastError)
;
; Returns:
;	ADO Error Text or Number. If no error detected, returns 0
*/
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

ADO_GetError(Conn, Text=1) {
	ErrorObj := Conn.Errors.Item(0)
	If !ErrorObj
	{
		Return 0
	}
	If Text
	{
		Return % ErrorObj.Description
	}
	Else
	{
		Return % ErrorObj.Number
	}
}


Sammoth :)
The sooner you fall behind, the more time you have to catch up.

tank
  • Administrators
  • 4345 posts
  • AutoHotkey Foundation
  • Last active: May 02 2019 09:16 PM
  • Joined: 21 Dec 2007
I get a blank response instead of a description. this doesnt seem to be an accurate full replacement. In my case i caused an sql error
Never lose.
WIN or LEARN.

wooly_sammoth
  • Members
  • 644 posts
  • Last active: Jul 03 2013 08:08 PM
  • Joined: 12 May 2009
yup you're right.
I had it working. just got to figure out what I did between having it working and posting it here.
give me a mo
The sooner you fall behind, the more time you have to catch up.

wooly_sammoth
  • Members
  • 644 posts
  • Last active: Jul 03 2013 08:08 PM
  • Joined: 12 May 2009
I've updated the first post with some improved functions which actually work (at least they have done in my tests so far).
I'm not sure what I'd done before but my mind was not completely on the task at hand so I'm going to award myself some leeway.

The problem which was occurring with the ADO_Read() function (ie. no error being returned) was because I was creating the RecordSet directly and bypassing creating a reference to the Connection Object. The Errors collection only exists for Connection Objects so I changed the function slightly to reference the Connection Object and hey presto, the errors are reported.

sammoth
The sooner you fall behind, the more time you have to catch up.

Carcophan
  • Members
  • 1578 posts
  • Last active: Nov 27 2013 06:46 PM
  • Joined: 24 Dec 2008
Just found this and gave it a try.

Copied your code exactly, only changes were adding a message box, and to the SQL and sDbFile code as mentioned in your notes

sDbFile := "U:\a\b\c\Tracker.accdb"
SQL := "SELECT Tech FROM [CoordNames]" {with and without the []}


I get no error messages, and I get no text in msgbox.



I am still kind of new to the AHKL and v2 worlds, and hope this isn't something simple I've overlooked, though I strongly feel it may be.



Can you provide a 'working example' of a message box or output from a DB to csv or something?

doyle
  • Members
  • 335 posts
  • Last active: Mar 02 2012 04:47 PM
  • Joined: 14 Nov 2007
I'd like the ADO functions to work too.

I'm just trying out AHK_L and my key script doesnt work. It's got about 50 SQL queries based on Seans COM, and ADO functions.

I dont mind converting them to AHK_L when theres a viable alternative.

btd
  • Members
  • 3 posts
  • Last active: Jan 31 2013 07:40 PM
  • Joined: 17 Aug 2012
Sammoth,
Your ADO_Write function was just what I needed for moving some csv data into an MS Access database. Took me a while to tweak the SQL statement, but once that was done, your function performed magnificently. Thanks very much for sharing!

-BTD