Reading Access 2007 database contents

[Arcee_uk]

Hi,

I'm pretty new here. I am try to read a database file made in Access 2007 (with a accdb extention and not the more common .mdb).

I found a old post about reading data from the db but I cannot get it toowork. The program opens the database then seems to do nothing and the closes it off. It does not appear to displauy any text in the debug window as I expected it too..

The code l0oks like this:

Code: Select all

; Open Main database file

Result = UseSQLiteDatabase()
If Result=0
MessageRequester("Cannot init database","Cannot Initalise database.",#PB_MessageRequester_Ok)
End
EndIf


Result = OpenDatabase(#PB_Any,"Data\mdb.accdb","Prefill","") 

TableName.s = "Prefill" 

If DatabaseQuery(Result, "SELECT * FROM [" + TableName.s + "]") 

  Columns= DatabaseColumns(Result) ; How many columns/fields (across) in the table 
EndIf      
  
; Now take the same sub-database and display it's contents! 

    Count1 =1 
    
   If DatabaseQuery(Result, "SELECT * FROM [" + TableName.s + "] ORDER BY [ID]") 
      
     While NextDatabaseRow(Reslt) 
            
      For Count= 0 To 41 
            
       DumpString.s = GetDatabaseString(Result, Count) 
       Debug "Count1 = "+Str(Count1)+"  "+DumpString.s 
       DumpString.s = "" 
              
                          
      Next Count 
      
      
        Count1 = Count1 + 1 
    
     Wend 
        
   EndIf

MessageRequester("Database found","Database opened.",#PB_MessageRequester_Ok)

I suspect I have done something really simle which I am just not wawarte of.

If anyone has any ideas I really appreicate it.

Many thanks

Arcee

[ts-soft]

> Result = UseSQLiteDatabase()
is only for SQLite3
with UseODBCDatabase() you can use the database. DSN and driver is required
(Microsoft Access Driver (*.mdb, *.accdb) 12.00.6211.1000 for example)

[Arcee_uk]

> Result = UseSQLiteDatabase()
is only for SQLite3
with UseODBCDatabase() you can use the database. DSN and driver is required
(Microsoft Access Driver (*.mdb, *.accdb) 12.00.6211.1000 for example)

Sorry a little lost now.

If I change the line above to Result = OpenODBCDatabase() then Purebasic fails to open the database file. If I leave it as OpenSQLiteDatabase then it opens.

As I have the full version of Office 2007 (thanks to a home use licence from work) I should have all drivers required to open databases surely. So why does the ODBC command not work?

I even tried opening the databse with Result = OpenDatabase(#PB_Any,"Data\mdb.accdb","Prefill","",#PB_Database_ODBC) but that also fails.

I am obviously missing something I just don't know what.

Thanks

Arcee

[Seldon]

OpenODBCDatabase() is not a PB function. To open a database via ODBC you need to add a connection to it. You can do it manually using the ODBC data source administrator tool (look for it in the Control Panel -> Administrative Tools ) . Or you can set it by your program and leave it or remove it when you quit.

Here it is a sample found in the forum with some functions to add and remove an ODBC connection (I don't remember the original authors ! ) :

Code: Select all

Declare AddConnection(Driver.s, ConnectString.s) 
Declare RemoveConnection(Driver.s, DSN.s)

i=UseODBCDatabase()
If i=0
 End
EndIf

; apre e/o collega la base di dati
tmp$="access.mdb"  ; folder+name of your Access file
DSN$="MYACCESSDB"
i=AddConnection("Microsoft Access Driver (*.mdb)","Server=127.0.0.1;Description=My ODBC Access;DSN="+DSN$+";DBQ="+tmp$+";UID=;PWD=;")

If i
  H_db=OpenDatabase(0,DSN$,"","")
  If H_db
    Debug "OK"
  EndIf
EndIf

Procedure AddConnection(Driver.s, ConnectString.s)
  Protected res
  res=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver,ConnectString) 
  If res 
    ProcedureReturn(1)
  EndIf 
EndProcedure 

Procedure RemoveConnection(Driver.s, DSN.s)
  Protected res
  res=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver,"DSN="+DSN) 
  If res
    ProcedureReturn(1)
  EndIf 
EndProcedure

[Fluid Byte]

I am trying to open a MS Access 2007 database as well now but the above code doesn't seem to work for me.

Simplified version:

Code: Select all

EnableExplicit

#ODBC_ADD_DSN = 1
#ODBC_CONFIG_DSN = 2
#ODBC_REMOVE_DSN = 3
#ODBC_ADD_SYS_DSN = 4
#ODBC_CONFIG_SYS_DSN = 5
#ODBC_REMOVE_SYS_DSN = 6
#ODBC_REMOVE_DEFAULT_DSN = 7

Procedure AddConnection(Driver$,ConnectString$)
	ProcedureReturn SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver$,ConnectString$)
EndProcedure

Procedure RemoveConnection(Driver$,DSN$)
	ProcedureReturn SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver$,"DSN=" + DSN$)
EndProcedure

UseODBCDatabase()

Define Result
Define Database$ = "C:\Users\Christian\Desktop\Datenbank1.accdb"
Define DSN$ = "MYACCESSDB"

Result = AddConnection("Microsoft Access Driver (*.mdb)","Server=127.0.0.1;Description=My ODBC Access;DSN=" + DSN$ + ";DBQ=" + Database$ + ";UID=;PWD=;")

If Result
	Result = OpenDatabase(0,DSN$,"","")
	
	If Result
		Debug "Database successfully loaded!"
	EndIf
EndIf

RemoveConnection("Microsoft Access Driver (*.mdb)",DSN$)

[Marco2007]

Replace Microsoft Access Driver (*.mdb) with Microsoft Access Driver (*.mdb, *.accdb).

[Fluid Byte]

Ohhh...

Thanks man, you saved my "behind"

Here's the updated version. You just need to adjust the DB details to your needs:

Code: Select all

EnableExplicit

#ODBC_ADD_DSN = 1
#ODBC_CONFIG_DSN = 2
#ODBC_REMOVE_DSN = 3
#ODBC_ADD_SYS_DSN = 4
#ODBC_CONFIG_SYS_DSN = 5
#ODBC_REMOVE_SYS_DSN = 6
#ODBC_REMOVE_DEFAULT_DSN = 7

Procedure AddConnection(Driver$,ConnectString$)
	ProcedureReturn SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver$,ConnectString$)
EndProcedure

Procedure RemoveConnection(Driver$,DSN$)
	ProcedureReturn SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver$,"DSN=" + DSN$)
EndProcedure

UseODBCDatabase()

Define Result
Define DSN$ = "MYACCESSDB"
Define Database$ = "C:\Users\Christian\Desktop\TestDB.accdb" ; Use ".\TestDB.accdb" for relative path
Define UserID$ = ""
Define Password$ = ""
Define Table$ = "test"
Define Column$ = "test"

Result = AddConnection("Microsoft Access Driver (*.mdb, *.accdb)","Server=127.0.0.1;Description=My ODBC Access;DSN=" + DSN$ + ";DBQ=" + Database$ + ";UID=" + UserID$ + ";PWD=" + Password$ + ";")

If Result
	Result = OpenDatabase(0,DSN$,"","")
	
	If Result
		If DatabaseQuery(0,"SELECT " + Column$ + " FROM " + Table$)
       
         While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
         Wend
      
         FinishDatabaseQuery(0)
      EndIf

		CloseDatabase(0)
	EndIf
EndIf

RemoveConnection("Microsoft Access Driver (*.mdb, *.accdb)",DSN$)

[Arcee_uk]

I have just tired to use that code to access one of my .accdb files but I am getting an error on this command:

Code: Select all

FinishDatabaseQuery(0)

Purebasic is saying: FinishDatabaseQuery() is not a function, array, macro or linked list/

Any ideas why, I just cut and pasted the code as I assumed it would work as a stand alone system to prove the database can be read.

Thanks
Arcee.

[srod]

What version of PB are you using?

Upgrade to PB 4.31 or the latest beta of 4.4.