Jump to content

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

AccLib Access Database


  • Please log in to reply
6 replies to this topic
trksyln
  • Members
  • 1 posts
  • Last active: Sep 25 2014 08:46 PM
  • Joined: 01 Mar 2013

heyy guys.. first sry for my english..

 

i was looking for a way to use MS ACCESS Databases with Autohotkey.. and i found a script 'ADOSQL' it was good but.. there was too much code and made me confused.. and modifed totaly.. now working.. at least how i like and i wanted to share this because i always use this web site for help  happy.png

 

First Connection:

#include AccLib.ahk
global dbfile:="TestDB.mdb"    ;to set database file
global dbpassword:="Password"    ;for password ofc

SELECT (example with 'select' others work same way)

AccLib("SELECT * FROM Table_Name")

 

 OR

query= 
 (
 SELECT * FROM Table_Name 
 )
AccLib(query)

 INSERT

query =
(
INSERT INTO Table_Name (colname1, colname2, colname3, colname4)
VALUES ( 'value1', 'value2','value3', 'value4' )
)
AccLib(query)

 UPDATE

query =
(
UPDATE Table_Name SET colname1='value1', colname2='value2', colname3='value3', colname4='value'  Where id = 5 ;which id query you wanna edit
)
AccLib(query)

READ from Database

;Read["column name",query num]

;Example:

AccLib("SELECT * FROM Table_Name")
value := % Read["id",1]

msgbox %value%

;or only

msgbox % Read["id",1]

 

READ from Database for more than one query :

AccLib("SELECT * FROM Table_Name")
loop,%total%   ;if you use with loop, total will give you total number of database query
  {
  id := % Read["id",a_index]
  }

Get total query number

AccLib("SELECT * FROM Table_Name")
msgbox %total%

Download link here.. i made a example file and put in it with a example database i hope you like it ;)https://code.google....ib.rar&can=2&q=



netkeflyn
  • Members
  • 1 posts
  • Last active: Dec 02 2013 10:20 PM
  • Joined: 15 Nov 2013

I have been using AccLib with AHK for a while now, but I am having some issue with the sample program modified for use with our databases.  It acts like it does not want to add or modified a database that has pre-existing information.

 

It gives me an Insert Failed error, but it temporarily writes the data, just starting back at gid 1 (which already has data and therefore fails)

 

Here is a copy of my add code.  Note that because there are different tables, I had to make the buttons store a variable to make If statements work.

 

addsave:
 SetTimer,addsave1,1
 return
 addsave1:
 SetTimer,addsave1,off
 gui,addmenu:submit,nohide
 if(aDie_Num = "" or aCust = "" or aCar_Desc = "" or aCombo = "")
 {
  MsgBox, 4096,Error, Fill form first!
  return
 }
 query_add =
 (
 INSERT INTO FlexoDie ( Die_Num, Cust, Combo, Car_Desc, LasNum, VenNum, Num_UP, ShtInf, Update, DiePur )
 VALUES ( '%aDie_Num%', '%aCust%', '%aCombo%', '%aCar_Desc%', '%aLasNum%', '%aVenNum%', '%aNumUP%', '%aShtInf%', '%aUpdate%', '%aDiePur%' )
 )
 AccLib(  query_add)

 get_Die_Num=
 (
 SELECT * FROM FlexoDie Order by Die_Num
 )
 AccLib(get_Die_Num)
 Die_Num2 := % Read["Die_Num",1]
 LV_Add("",Die_Num2,aCust,aCar_Desc,aCombo,aLstRkn,aLstRknIm)
 gui,addmenu:destroy
 return

 

I have problems with the update too, but they may be part of this error.  Any help would be appreciated.



burton666
  • Members
  • 186 posts
  • Last active: Jul 05 2017 05:35 AM
  • Joined: 10 Aug 2012

I am trying to understand but don't quite get it. I can get the total numbers of "rows" and get the info from one specific "cell". But the database I want to use with ACClib looks something like this:

 

ID123 | 2014-01-01 | Name1 | Completed

ID124 | 2014-01-01 | Name2 | Completed

ID125 | 2014-01-04 | Name99 | Not Completed

ID126 | 2014-01-04 | Name44 | Not Completed

 

If I want to show rows  with the date "2014-01-01" how would that look like?



gamefreak310
  • Members
  • 55 posts
  • Last active: Jul 12 2016 04:37 PM
  • Joined: 05 Apr 2007

is it posible to use  BEGIN TRANSACTION ?



gamefreak310
  • Members
  • 55 posts
  • Last active: Jul 12 2016 04:37 PM
  • Joined: 05 Apr 2007

i love to use acclib, but i have a problem when i try to update i get an error but it updates correct how can i fix this

 

image of error = http://s3.postimg.or...hrfxv/error.png

Wijzigen:
gui submit, nohide

gosub getwielistview

if snijdenwie != %snijdenwieorigineel%
{
querymaken := querymaken . " snijdenwie='" snijdenwie "',"
}
if laserwie != %laserwieorigineel%
{
querymaken := querymaken . " laserwie='" laserwie "',"   
}
if sgwie != %sgwieorigineel%
{
querymaken := querymaken . " sgwie='" sgwie "',"  
}
if ebwie != %ebwieorigineel%
{
querymaken := querymaken . " ebwie='" ebwie "',"  
}
if plooienwie != %plooienwieorigineel%
{
querymaken := querymaken . " plooienwie='" plooienwie "',"
}
if persenwie != %persenwieorigineel%
{
querymaken := querymaken . " persenwie='" persenwie "',"  
}
if diepdrukwie != %diepdrukwieorigineel%
{
querymaken := querymaken . " diepdrukwie='" diepdrukwie "',"  
}
if puntlaswie != %puntlaswieorigineel%
{
querymaken := querymaken . " puntlaswie='" puntlaswie "',"  
}
if lassenwie != %lassenwieorigineel%
{
querymaken := querymaken . " lassenwie='" lassenwie "',"  
}
if tiglassenwie != %tiglassenwieorigineel%
{
querymaken := querymaken . " tiglassenwie='" tiglassenwie "',"  
}
if zagenwie != %zagenwieorigineel%
{
querymaken := querymaken . " zagenwie='" zagenwie "',"  
}
if borenwie != %borenwieorigineel%
{
querymaken := querymaken . " borenwie='" borenwie "',"  
}
if slijpenwie != %slijpenwieorigineel%
{
querymaken := querymaken . " slijpenwie='" slijpenwie "',"  
}
if insertswie != %insertswieorigineel%
{
querymaken := querymaken . " insertswie='" insertswie "',"  
}
if riveterenwie != %riveterenwieorigineel%
{
querymaken := querymaken . " riveterenwie='" riveterenwie "',"  
}
if tappenwie != %tappenwieorigineel%
{
querymaken := querymaken . " tappenwie='" tappenwie "',"  
}
if soevereinenwie != %soevereinenwieorigineel%
{
querymaken := querymaken . " soevereinenwie='" soevereinenwie "',"  
}
if diversewie != %diversewieorigineel%
{
querymaken := querymaken . " diversewie='" diversewie "',"  
}
else
{
}

StringTrimRight, querymaken,querymaken, 1

querystart = UPDATE Bons SET
queryend = Where id = %currentid%
querymaken :=  querystart . " " querymaken  . " " queryend
msgbox %querymaken%
clipboard = %querymaken%

AccLib(  querymaken)
querymaken=


trayinfo = Succesvol aangepast
gosub tray

return


gamefreak310
  • Members
  • 55 posts
  • Last active: Jul 12 2016 04:37 PM
  • Joined: 05 Apr 2007

I want to search in my database for all parts that contains the name GON in it anyone know how to fix ?

;AccLib("SELECT * FROM Afgewerkt WHERE Onderdeel LIKE 'GON_KRLD60D_SPO1_5' ") ; <====== this works fine
AccLib("SELECT * FROM Afgewerkt WHERE Onderdeel LIKE '*GON*' ")  ; <====== <= why is this not working

loop,%total%
{
id= % Read["id",A_Index]
onderdeel= % Read["onderdeel",A_Index]
Datum= % Read["Datum",A_Index]
Nest= % Read["Nest",A_Index]
Materiaal= % Read["Materiaal",A_Index]
Machine= % Read["Machine",A_Index]
msgbox %onderdeel% - %id% - %total% 
}
return


					
					

Albireo
  • Members
  • 558 posts
  • Last active: Dec 13 2019 02:02 PM
  • Joined: 01 Feb 2006

I like the idea of this solution.
The description and the example, is clear.
But when I try to read the database - it doesn't work...
 

I got an error .: "Failed to connect database!"

 

Is it because I do not have Access installed on this computer?
Is it possible to resolve by copying some files to test on this computer?
 

On this computer, I have other SQL tables that can be read with a program "PkvViewer"

 

//Jan