Therefore I searched for a solution and were glad to see that PureBasic was able to fulfill (nearly) all my needs.
It is my aim to translate my Access-Applications into PureBasic, but the data should still be stored in Access-Databases.
The advantage I see in this is that I can easily view the data and therefore be able to check them for errors. Furthermore with PureBasic I can not only access tables but also queries.
My problem at the beginning was, that I couldn’t find extensive information on how access Access-Databases.
I needed several days of research in different boards to find out how to do it. Furthermore I also found out that I was not the only one with these problems.
Because of this I decided to compile everything I could find needed to access Access-Databases in order to save others the hard work of collecting these information from different sites all over the web.
Code: Select all
Enumeration 1
#ODBC_ADD_DSN ; // Add data source
EndEnumeration
Procedure.s AddDSN(databasename.s, user.s = "", pass.s = "", driver.s = "") ; Result DSN
; This Procedure is nessessery to acces an ACCESS-Database.
Protected name.s, strDriver.s, strAttributes.s
Protected L.l, result.l
Protected *buffer.Character
; generate database name (DSN)
name.s = "Pure_Viewer"
; choose driver automatically on error
If driver = ""
driver = GetExtensionPart(databasename)
EndIf
; Choose database driver
Select UCase(driver)
Case "ACCESS", "MDB"
strDriver.s = "Microsoft Access Driver (*.mdb)"
; Attributes zustellen
strAttributes.s = "Server=APServer;Description=" + name
strAttributes.s + ";DSN=" + name
strAttributes.s + ";DBQ=" + databasename
strAttributes.s + ";UID=" + user
strAttributes.s + ";PWD=" + pass + ";"
Default
strDriver.s = driver
EndSelect
*buffer = @strAttributes
For L = 0 To Len(strAttributes) - 1
If *buffer\c = ';'
*buffer\c = 0
EndIf
*buffer + 1
Next L ; Check the next byte
result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, strDriver, @strAttributes) ; Call the function you need from the ODBC library with the right details
If result
ProcedureReturn name
Else
ProcedureReturn ""
EndIf
EndProcedure
;-------------------------------------------------------------------------------------------
; First Database access
User$ = ""
Password$ = ""
Path.s = C:\Folder\databasename.mdb"
Base.s = AddDSN(Path,Benutzer$,Passwort$) ; Caling the procedure, that allows accessing the database.
UseODBCDatabase() ; opens the Database
If Base
If OpenDatabase(0, Base, User$, Password$)
Else
MessageRequester("Won’t work that way.", "The Database databasename.mdb has to be in directory "+Directory, #PB_MessageRequester_Ok)
End
EndIf
EndIf
;-------------------------------------------------------------------------------------------
; Select Record by criteria
SQL = "SELECT * FROM Tabellenname WHERE Tabellenfeld1 = "+ Chr(39) + Variable$+ Chr(39) +"order by Tabellenfeld2 asc, Tabellenfeld3 asc" ; Ermittelt alle Einträge in der Tabelle
DatabaseQuery(0, SQL) ; SQL Befehl ausführen
Z = 0
While NextDatabaseRow(0) ; alle Einträge durchlaufen
Z = Z + 1
Inhalt_Tabellenfeld1$(Z) = GetDatabaseString(0, 0)
Inhalt_Tabellenfeld2$(Z) = GetDatabaseString(0, 1)
Inhalt_Tabellenfeld3$(Z) = Str(GetDatabaseLong (0,2)
;usw
Wend
;-------------------------------------------------------------------------------------------
; Delete Record
SQL = "DELETE FROM Tabellenname WHERE Feldname = "+ Chr(39) + Variable$+ Chr(39) ; Ermittelt alle Einträge
DatabaseQuery(0, SQL) ; SQL Befehl ausführen
;-------------------------------------------------------------------------------------------
; Update Record
If Variable$ = “”: Variable$ = “ “ : Endif ; If NULL –Value Uptdate is not possible
SQL = "UPDATE Tabellenname SET Tabellenfeld ="+ Chr(39) +Variable$+ Chr(39)
SQL = SQL + "WHERE Tabellenfeld ="+ Chr(39) + Variable2$+ Chr(39)
DatabaseQuery(0, SQL)
;-------------------------------------------------------------------------------------------
;insert Record
If Variable1$ = “”: Variable1$ = “ “ : Endif ; If NULL –Value Insert is not possible
SQL = "INSERT INTO Tabellenname (Tabellenfeld1, Tabellenfeld2) VALUES (" + Chr(39)+Variable1$+ Chr(39) +","+ Chr(39)+Variable2$+ Chr(39)+")"
DatabaseQuery(0, SQL) ; SQL Befehl ausführen
Should data be updated in a query, the query property must be set to “Dynaset”. An update cannot happen if this is not the case.
There is one problem I wasn’t able to solve:
I could not find a possibility to access a table element with WHERE if this element is declared as a number (float, integer etc). Using the exact same SQL-Query on an element declared as text, no error occurs and the query is handled properly.
Maybe someone of you can help me in this matter.
Thanks in advance, NLink
