Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Sep 21, 2019 8:49 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Access database connection / usage
PostPosted: Sat Apr 30, 2011 9:31 am 
Offline

Joined: Mon Feb 21, 2011 4:41 pm
Posts: 27
Good day.
I have been searching the help docs, and this site, but I am still not clear on the best practice for persisting an Access database connection thru the application.
Do I open the database in the app class? How do I then utilize that database connection with recordset inserts, updates, selects, etc?
Is there any clear document or tutorial for this?

Thanks!

_________________
Ryan S.

"Make someone other than yourself your mission in life." J. Klunder - 2009


Top
 Profile  
Reply with quote  
 Post subject: Re: Access database connection / usage
PostPosted: Sat Apr 30, 2011 1:52 pm 
Offline

Joined: Tue May 15, 2007 4:27 pm
Posts: 10
You can open a connection with ADODB:

adodbConnection=new OLEObject("ADODB.Connection")

then
adodbConnection.open(connectionString)

For the connection string, you can use

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
connectionString = connectionString + "Data Source=" + sConnectionPath +";"
connectionString = connectionString + "User Id=admin;Password=;"

or

connectionString = "DRIVER={Microsoft Access Driver (*.mdb)};"
connectionString = connectionString+"DBQ=" + sConnectionPath+";"
if ExclusiveFlag > 0 then
connectionString = connectionString + "Exclusive=1;"
end if
connectionString = connectionString + "ExtendedAnsiSQL=1;"
connectionString = connectionString + "UID=admin;PWD="+password+";"



To query the db, you create a query object with this:

adoCommand=new OLEObject("ADODB.Command")

then

adoCommand.ActiveConnection=adodbConnection
adoCommand.commandText=theQuery


adoRecordSet= new OLEObject("ADODB.Recordset")
adoRecordSet.LockType = lockType
adoRecordSet.Open( adoCommand)

Now, your adoConnection, adoCommand and adpRecordset supports the methods availables to those objects, as you can see them in VBA, so you can write

= to get the value of a field:
return adoRecordSet.Fields.item(theName).value("value")

= to move to the next record:
adoRecordSet.MoveFirst

...

Normally, you should be able to use the ODBCDatabase to connect to msAccess. I tried that in the past, in an attempt to get rid of my custom classes, but I had to revert the changes since using ODBCDatabase to connect to msAccess does not (was not supporting ?) UTF-8 which was a key requirement in my case.

Suggestion: create one class to embed you adoConnection and another class to embed your adoRecordSet and implement at connection and recordset level the part of the interface the existing databse and Recordset do provide, to make your life easier if you may to switch from your custom classes to the standard classes

Also, avoid connecting / disconnecting each time you need to run a query, so I suggect you add a property to app to hold your active connection, or create a singleton with shared properties / methods to handle the connections (cleaner than the property in app)

Hope this helps...

Serge


Top
 Profile  
Reply with quote  
 Post subject: Re: Access database connection / usage
PostPosted: Sat Apr 30, 2011 2:37 pm 
Offline

Joined: Mon Feb 21, 2011 4:41 pm
Posts: 27
Serge, fantastic!
That is a huge help, and gets me going in the right direction.
Thanks for your time!

_________________
Ryan S.

"Make someone other than yourself your mission in life." J. Klunder - 2009


Top
 Profile  
Reply with quote  
 Post subject: Re: Access database connection / usage
PostPosted: Sat Feb 02, 2013 2:56 pm 
Offline

Joined: Mon Jan 21, 2013 6:05 pm
Posts: 18
Yes, but will that connect to an Access 2010 database?
I've had success with this approach only with Access 2002 to 2007.


Top
 Profile  
Reply with quote  
 Post subject: Re: Access database connection / usage
PostPosted: Sun Feb 03, 2013 12:29 am 
Offline

Joined: Mon Jan 21, 2013 6:05 pm
Posts: 18
I found the answer for opening an Access 2010 database in RS 2012r2.1:
Dim adodbConnection as new OLEObject( "ADODB.Connection" )
Dim adodbCommand as new OLEObject( "ADODB.Command" )
Dim SQL as string
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; DataSource= yourDatabase .accdb; Persist Security Info=False;"
adodbConnection.Open(connectionString )
SQL = "SELECT ID,field1,field2,field3,field4,field5 FROM yourTable"
adodbCommand.ActiveConnection = adodbConnection
adodbCommand.CommandText = SQL
adodbRecordSet = adodbCommand.Execute
.
.
.etc.
Thanks for the help from Eugene Dakin http://drdakin.tripod.com/REALbasic/REALbasic.htm


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group