Pull data from Access .mdb file

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Grendahl
Posts: 159
Joined: 30 Sep 2013, 08:21

Pull data from Access .mdb file

06 Aug 2014, 14:57

I've been beating my head against this wall for a while, and cannot seem to get any connectivity to an Access 2010 .mdb file.

I'm hoping to just be able to run a query or two against the ReportData table in this file:
test.mdb

Currently I'm using the latest 64-bit Unicode _L, but could compile as a 32-bit if need be.

Any help would be greatly appreciated!

(And yes, I've trolled this forum as well as our old one, and none of the examples work for me. I have no issue using ODBC connectivity to MS SQL with AHK.)
User avatar
jNizM
Posts: 2674
Joined: 30 Sep 2013, 01:33
GitHub: jNizM
Contact:

Re: Pull data from Access .mdb file

07 Aug 2014, 00:42

here is an example for an ups mdb file I use
maybe it helps
you need this: DBA by IsNull

Code: Select all

; GLOBAL SETTINGS ===================================================================

#Warn, LocalSameAsGlobal, Off
#NoEnv
#SingleInstance Force
SetWorkingDir %A_ScriptDir%
#Include <DBA>

; SCRIPT ============================================================================

Gui, Margin, 10, 10
Gui, Font, s10, Tahoma
Gui, Add, Text, xm ym, Microsoft Access Database (.mdb)

Gui, Add, Text, xm yp+30 w130, Lieferscheinnummer
Gui, Add, Edit, x+0 yp-2 w170 h22 vLS, LS4128556
Gui, Add, Button, x+2 yp-1 w80 h24 gResultLS, Result

Gui, Add, Text, xm yp+50 w130, Trackingnummer
Gui, Add, Edit, x+0 yp-2 w170 h22 ReadOnly vTRN,
Gui, Add, Text, xm yp+30 w130, Lieferscheinnummer
Gui, Add, Edit, x+0 yp-2 w170 h22 ReadOnly vLSN,
Gui, Show, AutoSize, Microsoft Access Database (.mdb)
return

ResultLS:
	Gui, Submit, NoHide

	loop, Z:\Apps\UPS\archiv\*.mdb
	{
		database := A_LoopFileFullPath
		connectionString := % "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . database . ""
		db := DBA.DataBaseFactory.OpenDataBase("ADO", connectionString)

		sql := % "SELECT calPackage.Sm_trackingNo "
                . "FROM calPackage "
                . "INNER JOIN calInternationalShipment "
                . "ON calPackage.m_foreignKey = calInternationalShipment.m_foreignKey "
                . "WHERE calInternationalShipment.Sm_merchandiseDesc LIKE '" . LS . "%'"

		rs := db.OpenRecordSet(sql)
		if(!rs.EOF)
		{
			SetTimer, nothing, off
			global Tracking := % rs[1]
			global Foundbase := % database
			GuiControl, +c22AA55, TRN
			GuiControl,, TRN, % rs[1]
			rs.Close()

			connectionString := % "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . Foundbase . ""
			db := DBA.DataBaseFactory.OpenDataBase("ADO", connectionString)

			sql := % "SELECT calInternationalShipment.Sm_merchandiseDesc "
					. "FROM calInternationalShipment "
					. "INNER JOIN calPackage "
					. "ON calInternationalShipment.m_foreignKey = calPackage.m_foreignKey "
					. "WHERE calPackage.Sm_trackingNo LIKE '" . Tracking . "'"

			rs := db.OpenRecordSet(sql)
			if(!rs.EOF)
			{
				GuiControl,, LSN, % rs[1]
				rs.Close()
			}
			else
			{
				GuiControl,, LSN, Nothing Found!
				rs.Close()
			}
			rs.Close()
			break
		}
		else
		{
			SetTimer, nothing, -12000
			GuiControl, +cFF7800, TRN
			GuiControl,, TRN, Search...
			rs.Close()
		}
		rs.Close()
	}
return

nothing:
	GuiControl, +cDA4F49, TRN
	GuiControl,, TRN, Nothing Found!
	GuiControl,, LSN,
return

; EXIT ==============================================================================

GuiClose:
GuiEscape:
	rs.Close()
	ExitApp
[AHK] 1.1.33.02 x64 Unicode | [WIN] 10 Pro (Version 20H2) x64 | [GitHub] Profile
Donations are appreciated if I could help you
magusneo
Posts: 37
Joined: 30 Sep 2013, 06:34

Re: Pull data from Access .mdb file

13 Aug 2014, 20:17

How can I open a mdb file has password protection with DBA?I added "password=mypass;" in connectstring,but it reports some lock error.

edit:Never mind,resolved.

Return to “Ask For Help”

Who is online

Users browsing this forum: alexsu, Bing [Bot], ConTrast77, Spawnova, XMCQCX and 45 guests