Re: Help with Insertdatabase command

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for this Peter

I have tried to set up a file dsn following the guidance on msdn and the
inscreen instructions

This is what I have ended up with as my clients.dsn file dsn:

[ODBC]
DRIVER=Firebird/InterBase(r) driver
UID=RO
CHARSET=NONE
Dbname=e:\ibdata\live.fdb

Although it let me create it, if i go into configure it it comes up with the
error 'general error invalid file dsn'

If i try and connect to it in Word either stepping through it manually using
the 'insert database' button in word and then using ms query I get the error
'Your username and password are not defined. ask your administrator to set up
a firebird login'.

IF I try dialog or database engine failures and then 'word could not open
the data source'

I have tried including the password in the file dsn but this does not help.

Am I missing something in the nature of the ODBC connector that the firebird
database allows, or is it something the way i am setting up the file dsn do
you think?


This is the code I was trying to connect with

odatasource = "C:\Program Files\Common Files\ODBC\Data Sources\clients.dsn"

ologin = "FILEDSN=" & odatasource & ";PWD=IA;UID=RO"

oSQL = "SELECT CLIENTS.CL_KEY, CLIENTS.CL_NAME " & _
" WHERE CLIENTS.CL_KEY='" & code & "'"

Selection.Range.InsertDatabase _
LinkToSource:=False, _
Connection:=ologin, _
SQLStatement:=oSQL, _
DataSource:=odatasource, _
IncludeFields:=False

--
With best regards

Liam



"Peter Jamieson" wrote:

As far as I am aware, you have to provide InsertDatabase with a file name
(and it will almost certainly have to be a file you can reach on a local
device or via Windows networking, not an Internet URL) in the Datasource
parameter, regardless of whether or not the connection string provides
everything you need to connect to your data source.

In this case, the only thing that is likely to work is to create a file dsn
for your data source, let's call it

"c:\mydsns\ibdsn1.dsn"

and use, something like:

oDataSource = "c:\mydsns\ibdsn1.dsn"
oLogin = "FILEDSN=" & oDataSource & ";PWD=CA;UID=RO"
oSQL = "SELECT CLIENTS.CL_KEY, CLIENTS.CL_NAME " & _
" WHERE CLIENTS.CL_KEY='" & code & "'"
Selection.Range.InsertDatabase _
LinkToSource:=False, _
Connection:=oLogin, _
SQLStatement:=oSQL, _
DataSource:=oDataSource, _
IncludeFields:=False

Since file DSNs are plain text files, they are reasonably easy to create "on
the fly" should you need to do so, but if you end up doing that, it's very
important that the driver name in the DSN matches the driver name on the
machine where the code is running.

No, I don't think this stuff is properly documented anywhere, and of course
that means there could well be a way to do it without a file DSN...

Peter Jamieson

"Liam" <Liam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:67F2F401-3473-4479-8296-0CD963624DAF@xxxxxxxxxxxxxxxx
Hi there

With some help from user forum postings I have got the following command
to
retreive information from an sql database and use it to populate fields in
a
userform.

The trouble i am having is that when the code is executed, I cannot stop
the
'Select Data Source' Window from appearing. The three choices that are
presented are +Connect to new data source, +Connect to SQL server
connection
and +Thumbs. The first is highlihghted by defualt, and the user just needs
to
hit <Return> for the code to continue to run, but i would prefer that this
user intervention was not required. Any thoughts gratefully appreciated.

Here is the code i am using-



oLogin = "DSN=clients;Driver=Firebird/InterBase(r) driver;Dbname=" & _
"srv01:e:\ibdata\live.fdb;CHARSET=NONE;PWD=CA;UID=RO"

oSQL = "SELECT CLIENTS.CL_KEY, CLIENTS.CL_NAME " & _
" WHERE CLIENTS.CL_KEY='" & code & "'"

Selection.Range.InsertDatabase LinkToSource:=False, Connection:=oLogin, _
SQLStatement:=oSQL, IncludeFields:=False


--
With best regarads

Liam




.

Quantcast