Help with ODBC or other means to Access Access Database

[xProgrammer]

Hi all

I need to access a Microsoft Access database situated on a Windows box. Whilst ultimately I need to get the data to a Linux box the Linux/Windows thing isn't an issue. I could possibly run ODBC from the Linux box directly, but I figure it would be easier to write a program that collects the data from the Access database running on the Windows box itself. So I would like some help as to what is the best method for doing this and some example code if possible. My preference at this stage would be to use ODBC rather than directly control Access.

I need to be able to query the Access database fairly extensively, but probably not update it (although flagging something as "processed" might be nice). The data is patient information together with details / results of medical scans including some images.

I do have to be careful that I don't in any way intefere with the operation of the medical application that I am planning to get the information from.

Any help would be greatly appreciated.

Thanks
xProgrammer

[Enrico Maria Giordano]

Can you use ADO? If yes, please search for examples in this forum.

EMG

[xProgrammer]

Thanks for your advice Enrico.

I had thought in terms of ODBC given that there would be some chance of using it on Linux, but given that I can run this on the Windows box in question, if ADO is easier and there is more experience with it out there in the "FiveWin world" then that's the way to go.

I'll want to start off with a Connection object since I will need to make a number of queries on the Access database. I have been looking through the FiveTechSost forum as you suggested and googling. I was wondering what is the difference between:

Code: Select all  Expand view  RUN

oConnection := CreateObject( "ADODB.Connection" )

and

Code: Select all  Expand view  RUN

oConnection :=TOleAuto():New( "ADODB.Connection" )

I gather that perhaps both will work? I know that the first of these two worked with Microsoft Word, that is:

Code: Select all  Expand view  RUN

oWord := CreateObject( "Word.Application" )

but I'm sure I saw that others used the second form. I guess if both work does it really matter? I would like to know if there are advantages in either approach. Unfortunately I can't bring the medical equipment home with me so I don't have a Windows computer with Microsoft Access to play on just now.

Thanks
Doug
(xProgrammer)

[James Bott]

Doug,

This is the source of CreateObject function in Win32Ole.Prg of xHarbour
Code:

FUNCTION CreateObject( cString )
RETURN TOleAuto():New( cString )

CreateObject is a function which creates TOleAuto object by calling TOleAuto():New( )

So, you can use either one in xHarbour but I think you need to use TOleAuto() with Harbour. Or, looking at it the other way, you can use TOleAuto() with both xHarbour and Harbour.

Regards,
James

[Enrico Maria Giordano]

So, you can use either one in xHarbour but I think you need to use TOleAuto() with Harbour.

No, you can use CreateObject() in Harbour too. CreateObject() is a quasi-standard call that you can find in samples written in many other programming languages.

EMG

[xProgrammer]

Thanks Enrico and James

I always wondered which one was better but now I know that they are one and the same. CreateObject is possibly more "readable" if you are coming from another language such as VB.

I've done a bit of reading and it seems that there's a number of different ways of doing things in ADO but I have tried to figure out a basic skeleton - can't get to the target machine to do any real testing. But is this the sort of direction in which I have to head?

Code: Select all  Expand view  RUN

// Am I on the right track with this?

LOCAL oConnection
LOCAL sConnectionString
LOCAL oRecordSet
LOCAL SQLQuery
LOCAL oField

// establish connection
oConnection := CreateObject( "ADODB.Connection" )
sConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Wherever\Whatever.mdb; Persist Security Info=False"
oConnection:Open( sConnectionString )

// verify connection
? "Connection State", oConnection:State
? "Command Timeout", oConnection:CommandTimeout

// create recordset
oRecordSet := CreateObject( "ADODB.RecordSet" )

// execute query
sSQLQuery := "SELECT * FROM Locations"
oRecordSet:Open( sSQLQuery, oConnection )

// navigate through recordset
oRecordSet:MoveFirst()            // probably redundant
DO WHILE !oRecordSet:EOF
   FOR EACH oField in oRecordSet:Fields

      // do something
      ? oField:Name, "=", oField:Value

   NEXT
   oRecordSet:MoveNext()
ENDDO

// close recordset and connection
oRecordSet:Close()
oConnection:Close()

Once again thanks for your help
Doug

[xProgrammer]

The nice thing is that, having written a client server xBase database server entirely in xHarbour on linux, it shouldn't take much effort to port it to Windows. So if I can write the code necessary to query the Microsoft Access database I'll be able to query it from my Linux applications. And the only difference to the front end Linux application will be a different IP address for the queries that have to go to the Access database. Things might be falling into place!

Doug

[Enrico Maria Giordano]

But is this the sort of direction in which I have to head?

Yes.

EMG