ODBC Connection String

[Kenln]

Can I get away from or create programmitically an ODBC connector?

I have been using a System DSN which uses an iSeries ODBC Driver. I create Pass-Through queries pointing to this DSN.

I would like to elimitate the System DSN itself and have a query that has all the required parameters in the connection string itself. Obviously I need the driver but I would like to get away from an actual connector.

For security reasons the Network Admin can (I think) prevent people from creating new connectors. But once created anyone can use it anywhere. Such as in Excel see just about any table/file on the iSeries.

Any thoughts?

Thank you,

 

[Banana]

Google "DSN-less connection", and be sure to google for Carl Prothman, too; he has a great list of all possible connection, both DSN and DSN-less.

HTH.

 

[KeithG]

Check out the below link

http://www.connectionstrings.com/?carrier=as400

 

[Kenln]

I have tried so many combonations I lost count.
I can get the DSN popup to open (which I don't want), but even then it does not work.

I usuall get -7778 There is no message for this error.

Arrrggghhhh..... I'm lost.

 

[Banana]

1) Did you specify dbDriverNoPrompt setting?
2) Consult your driver's documentation to see how this can be prevented if you did.

 

[wazz]

another just in case: http://support.microsoft.com/kb/892490. attaches to sql svr. can't remember if it gets rid of the popup. (dbAttachSavePWD)

 

[Kenln]

Banana,

I do not know where to set that. I will look up documentation. All of the examples make is seem easy. One line, 4 parameters and that's it.

I thought it would be an easy fix.

 

[Kenln]

I found this really cool link

http://www-1.ibm.com/support/docview.wss?uid=nas1c52dfe451b37518086256931005499ff

But I am not sure how to use it to get a Pass-Through query to connect?

 

[KeithG]

Tje below should work if you fill in the detail

ODBC;Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;

 

[Kenln]

That is where I started. I still get
"Reserved Error - 7778 There is no message for this error."

 

[KeithG]

The ODBC errors are vague. I always just tweak the connection string until it works. I connect to iSeries all the time.

 

[Banana]

This is what I do...

Set MyDatabase= DBEngine(0).OpenDatabase("MyDatabase", dbDriverNoPrompt, True, strConnection)

strConnection being the full connection string including the prefix "ODBC;".

My code was modified from Doug Steele's example.. His example assumes a MS SQL backend, so you need to take out that connection string and replace with whatever you need to use from Carl Prothman's link.

HTH.

 

[Kenln]

Okay, this works but it is definately not what I am looking for

ODBC;DRIVER={iSeries Access ODBC Driver};SIGNON=0;XDYNAMIC=0;CMT=0;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=My Library;SYSTEM=My System;FILEDSN=C:\Junk\MyFileDSN.dsn;

But with this example I need to have "FILEDSN=C:\Junk\MyFileDSN.dsn" at the end and a real DSN that anyone can open with Notepad.

If I could use the above string without the last bit it would be grrrrrreat.

 

[Banana]

Hm, I'm lost.

You want to use a DSN for a DSN-less connection?

 

[Kenln]

No I do not want a DSN at all. I want a DSN-Less connection

Check this out.

If I make a bogus final parameter like
"FILEDSN=C:\asdfasdfasdfasdasd\MyFileDSN.dsn;"

Where the DSN does not exist it still works.

I am now wondering if this is safe to use (publish) or will it crash later on???


I do not understand it but I can use it.

 

[KeithG]

No he doesn't want the DSN but it the only way he can get it to work. I will tinker with my iSeries connection later today. I am pretty sure I have connected with out the DSN before.

 

[KeithG]

can you use a blank string for the DSN parameter?

 

[Banana]

OH!

Sorry. Not enough caffeine.

As I'm totally unfamiliar with iSeries and its particulars, the only advice I can give is to go and ask a forum dedicated to the backend for an answer; or maybe it's already documented in their manual for the driver?

 

[Kenln]

I tried "FILEDSN=;" and the Data Source prompt opens for me to create one.

I use "FILEDSN=C:\MyDSN;" which absolutely does not exist and it works.

 

[Banana]

It definitely sounds like an issue with the driver, not Access, so you'd have to complain to the drivers' developers.