I currently have a small VB program which I feed data into to take information and commands from my OI application, and feed them into an Access MDB database.

I would prefer to use OLE to create an Access connection.

Is this possible, and if so any pointers to where I might find information on how to do this.

Using OI 8, so the SQL connector is not yet a solution I can accommodate.

Colin Rule

Hi Colin,

You can have your way with Access using ADO or using the Accesses object model. ADO is probably best choice. Access DOM will tie you to version specific syntax. The ADO objects well documented on the web. There are a lot of details if you want it fast/reliable, but you can accomplish simple tasks pretty easily.

Some code/comments off the top of my head, not tested

* Connect

* see ConnectionString.com for the correct string

connection_string=Xlate('PARAMETERS', 'CONNECTIONSTRING', 1, 'X')

convert @tm to ' ' in connection_string

oConn=OleCreateInstance('ADODB.Connection')

oConn-]ConnectionString=connection_String

x=oConn-]Open()

*—

* Slow Update: Let ado create the recordset

* ADO will do the work to get datatypes correct

* You put data in fields, call update method at the end

*—

Sql=SELECT'

Sql := ' seq'

Sql := ', name'

Sql := ', text'

Sql := ', descrip'

Sql := ' FROM my_table'

Sql := ' WHERE seq is null'

rs=OleCreateInstance("ADODB.Recordset")

rs-]ActiveConnection=Oconn

rs-]CursorLocation=adUseClient

rs-]CursorType=adOpenStatic

rs-]LockType=adLockOptimistic

x= rs-]Open(Sql)

x=rs-]AddNew()

fields=rs-]Fields

i=1

this_fld=OleGetProperty(fields, 'Item',i-1)

this_fld-]Value=myseq

i+=1

this_fld=OleGetProperty(fields, 'Item',i-1)

this_fld-]Value=myname

i+=1

this_fld=OleGetProperty(fields, 'Item',i-1)

this_fld-]Value=mytext

i+=1

this_fld=OleGetProperty(fields, 'Item',i-1)

this_fld-]Value=mydesc

x=rs-]Update()

x=rs-]close()

rs='

* faster update

* Build Sql statement, send it in

* Limited to 255 characters, and with Access you have to fight their special rules for example dates might neesd #08-04-2010#

* you have to determine if you are doing an update or insert, when to use quotes or not

*

equ quote$ to "'"

Sql=UPDATE my_table'

Sql := 'setset text = : quote$ : name : quote$

Sql := 'set descrip= : quote$ : name : quote$

Sql := ' WHERE seq= : quote$ : seq : quote$

x=Oconn-]Execute(Sql)

* reading data is like the slow update - make a recordset from an sql statement, then get values instead of setting them. For a fast way to pull the data into OI, look at the recordset's getstring method.

- Bob

Thanks Bob

Looks like what I will need, I shall give it a try.

Colin

View this thread on the Works forum...