Using a File DSN EDITED 02/12/2008 14:42

AndyP · Post by **AndyP** » Wed Nov 19, 2008 6:01 pm

Is it possible to connect to an Access db using a file dsn?

I've done this before with other development software but cannot see how to acheive in Revolution.

File DSN example below.

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=no
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\mydb\
DBQ=C:\mydb\mydb.mdb

SparkOut · Post by **SparkOut** » Wed Nov 19, 2008 6:13 pm

In theory:

Code: Select all

get revOpenDatabase("odbc","theDSNfile",,"admin","adminpassword","cursortype")

Note the empty item between theDSNfile and username.

Rev dictionary wrote:To use a DSN to identify an ODBC database, use the DSN as the host, and leave the databaseName parameter empty.

AndyP · Post by **AndyP** » Thu Nov 20, 2008 1:33 pm

Thanks SparkOut, once again you come to my rescue.

AndyP · Post by **AndyP** » Fri Nov 21, 2008 5:39 pm

Well got ahead of myself there...have removed the --Solved --

How do I get the DSN info in here? (thebit in bold)

revOpenDatabase("odbc","theDSNfile",,"admin","adminpassword","cursortype")

I tried just putting the file location in but this did not work.

BvG · Post by **BvG** » Sat Nov 22, 2008 2:23 pm

I don't know much about databases, and nothing about dsn files. Said that, if you need to give rev the data within the file, maybe you should load it into memory first, then give that to the revOpenDatabase function:

Code: Select all

put url "file:path/to/dsnfile" into theDSN
get revOpenDatabase("odbc",theDSN,,"admin","adminpassword","cursortype")

SparkOut · Post by **SparkOut** » Sun Nov 23, 2008 12:28 pm

Hmmm, well I said "in theory"

And in theory, using the path to the DSN file should be correct - I don't think you have to load the contents.

I did some testing and I cannot make a File DSN work. I get an error:

[Microsoft][ODBC Driver Manager]Invalid string or buffer length

If I change the File DSN to a System DSN and just use the name of the System DSN, then I get a connection OK.

(You might have to work to stop it barfing on paths and/or table names with spaces in)

AndyP · Post by **AndyP** » Tue Nov 25, 2008 10:08 pm

Thanks SparkOut, BVG,
I will have a play with the paths etc and if I find a usable solution post it back here for reference.

AndyP · Post by **AndyP** » Tue Dec 02, 2008 1:01 pm

OK, I think I've exhausted all permatations and have come to the conclusion that FileDSN will not work with the ODBC Revolution dll (dbodbc.dll)

It's a shame because using a FileDSN is an easy way to add connectivity to an Access db, you need to just deploy the FileDSN file with the application.

Anyway moving on, what I've decided to do is to create a System DSN on the clients computer using writes to the registry. I'm going to post an article and example on how to do this later today in the 'Databases' section of the forums.

Posting is here:

EDITED 02/12/2008 14:42

http://forums.runrev.com/phpBB2/viewtopic.php?t=2441

mwieder · Post by **mwieder** » Sat Dec 06, 2008 2:35 am

Andy- thanks for doing the legwork on this. I think you're right - FileDSN seems broken in rev's driver. Have you filed a bug report on this?

AndyP · Post by **AndyP** » Mon Dec 08, 2008 2:10 pm

Hi mwieder

No I haven't, will do now.