Eureka!
- From: Liam <Liam@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 19 Dec 2006 04:47:01 -0800
I had a further play this morning, and tried including the password in the
fils dsn, and then connecting througe Excel/MSQuery which seemed to work.
So I then tried this file DSN through word/query and it seemed to work, and
it also worked through the VBA that you suggested, and without prompting the
user to select the datasource - Eureka!
What is slightly strange is that I then took the password out of the file
dsn (as it is in the VBA anway) it still worked... wierd.
Anyway for the sake of completion here is the FileDSN text:
[ODBC]
DRIVER=Firebird/InterBase(r) driver
UID=TFRO
CHARSET=NONE
Dbname=srv01:e:\ibdata\live.fdb
And the VBA is
Dim BMRange As Range
Dim oDatasource, Results, oLogin, oSQL As String
Set BMRange = ActiveDocument.Bookmarks("text").Range
oDatasource = "\\srv01\clients.dsn"
oLogin = "FILEDSN=" & oDatasource & ";PWD=IA;UID=RO"
oSQL = "SELECT CLIENTS.CL_KEY, CLIENTS.CL_NAME " & _
"WHERE CLIENTS.CL_KEY = '" & code & "'"
BMRange.InsertDatabase _
LinkToSource:=False, _
Connection:=oLogin, _
SQLStatement:=oSQL, _
DataSource:=oDatasource, _
IncludeFields:=False
Thanks Peter for all your help on this - this is much better then trying to
include a wait and then a sendkey...
--
With best regards
Liam
"Peter Jamieson" wrote:
Hi Liam,.
Yes, I see much the same as you do. Obviously, I only have a very basic
Firebird setup here and am logging in as SYSADM with the standard password.
I think it is significant that my file DSN is pointing to the correct
database file, but perhaps not.
If I were in your situation, I would probably try in the first instance to
connect via Excel (which generally connecs to data rather more easily than
Word) via MS Query, and see what happens. If you manage to do that OK (or
have already done so) I'd say it's a Word-specific problem.
Not sure where to go from here, but perhaps we can try again 2morrow...
Peter jamieson
"Liam" <Liam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:818399D6-EE6F-479F-AA15-090109DF17ED@xxxxxxxxxxxxxxxx
Thanks for this Peter
When I go into ODBC Data Source Administrator and set up a User DSN, and
add
the firebird driver I get quite a detailed window headed 'Firebird ODBC
Set
Up which asks for datbase name, driver, client, database account, password
and role.
In contrast when i add a File DSN after selecting firebird and then a name
for the file dsn, the window that pops up is quite a lot simpler, after
saying
'When you click finish you will create the data source which you have just
configured. The driver may prompt you for more information.
File Data Source
Filename: C:\clients3.dsn
Driver: Firebird/InterBase(r) driver
A window that then pops up headed Firebird ODBC Connect only asks for
Account, Password and Role, nothing else ie without clicking the advanced
tab
earlier on or editing in notepad there is no way of telling it where the
database is stored etc
I am not sure why this might be the case and whether it is causing some of
the problem.
I have checked the database filename and the saving as ANSI and stored the
FileDSN in somewhere with no spaces in the title.
A connection could not be made using the file data source parameters you
have entered
If I do click 'advanced' in the file DSN setup and input the database
name,
login, password, and charset and then click OK it doesn't come up with the
error message, but doesn't store the log in ID and PASSWORD there, so this
sounds promising,
If I then go into word and then try the insert datbase command manually,
then click tools, ms query, select the file DSN, it comes up with a screen
input login and password, it has the RO as the default login, but if i
type
in the password and then click ok it says
'Your user name and password are not defined. Ask your database
adminstrator
to set up a firebird login'
If I try it in the vba code, I either get the same message about setting
up
a firebird login or I get a window coming up called datalink properties
which
if i click through it comes up with the message about setting up a
firebird
login.
One strange think is the passord I input to get the USER DSN seems to have
some kind of encryption applied when reading it from the souce VBA after
recording a macro, into a long string. I have tried both the long string
and
the initial short word as the password to log in.
Do you have any other ideas?
This is what my file dns looked like
[ODBC]
DRIVER=Firebird/InterBase(r) driver
UID=TFRO
CHARSET=NONE
Dbname=e:\ibdata\live.fdb
--
With best regards
Liam
"Peter Jamieson" wrote:
Hi Liam,
Your .dsn file looks OK to me, as far as I can tell (since I don't have
an
Interbase ODBC driver here)except that judging from your original connect
string the Dbname string probably needs to change, i.e. try
[ODBC]
DRIVER=Firebird/InterBase(r) driver
UID=RO
CHARSET=NONE
Dbname=srv01:e:\ibdata\live.fdb
Other than that I will see if there is anything else I can spot at my
end.
Just to be on the safe side, try locating the .dsn on a path that doesn't
have any spaces in the name, and ensure the .dsn file is ANSI-encoded,
not
Unicode (e.g. open it and try to save it in Notepad).
Peter Jamieson
"Liam" <Liam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:811459E5-D488-463A-B4EE-1A9D985B363B@xxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: Eureka!
- From: Peter Jamieson
- Re: Eureka!
- References:
- Re: Help with Insertdatabase command
- From: Peter Jamieson
- Re: Help with Insertdatabase command
- From: Liam
- Re: Help with Insertdatabase command
- From: Peter Jamieson
- Re: Help with Insertdatabase command
- From: Liam
- Re: Help with Insertdatabase command
- From: Peter Jamieson
- Re: Help with Insertdatabase command
- Prev by Date: Re: One more question
- Next by Date: Re: Eureka!
- Previous by thread: Re: Help with Insertdatabase command
- Next by thread: Re: Eureka!
- Index(es):
Relevant Pages
|
