SQL syntax assistance on a Pass-Through query desparately needed...

I apologize in advance to forum readers noticing this somewhat
redundant post. I fear that my Subject Heading was ill-chosen in
earlier post I made on this topic. Am hoping that a clearer Subject
heading will attract the knowledgeable forum contributors I'm
seeking. Thank-you...

I'm being academically challenged trying to build a pass-through
query inside of Access 2.0 to "reach out 'n touch" a remote data
file running on a Linux box in Kansas. I was able to achieve this
goal from within Access 97 (thanks be to the higher powers) but
am suffering somewhat with Access 2.0. I'm certain that it can be
done, but I'm just not the right man for the job without some
assistance.


What I've tried in Access 2.0 is to open a query in design view
and select the "Query" item from the menubar. Then choose
"SQL Specific" and "Pass-Through". Then, in the properties
box for the query object, I invoke the builder button for the
"ODBC Connect String" property. I'm then prompted to
specify an SQL Data Source. That's where I lose it. I
can't seem to input the parms (see prev post) needed.

Error: ODBC call failed... [MicroSoft] [ODBC DLL] Datasource not
found and no default ODBC driver specified.

I've tried stuff like the following for the ODBCConnectStr Property...
ODBC;DSN=ABCD;SERVER=123.456.789.10;UID=UserName;P WD=MyPass
where ABCD is the dBase name on the remote server.

Somehow, my syntax just isn't getting through. Should I be passing
something else for the DNS and SERVER assignments? Where do
I go from here?

re: SQL syntax assistance on a Pass-Through query desparately needed...

MLH wrote:
[color=blue]
> I apologize in advance to forum readers noticing this somewhat
> redundant post. I fear that my Subject Heading was ill-chosen in
> earlier post I made on this topic. Am hoping that a clearer Subject
> heading will attract the knowledgeable forum contributors I'm
> seeking. Thank-you...
>
> I'm being academically challenged trying to build a pass-through
> query inside of Access 2.0 to "reach out 'n touch" a remote data
> file running on a Linux box in Kansas. I was able to achieve this
> goal from within Access 97 (thanks be to the higher powers) but
> am suffering somewhat with Access 2.0. I'm certain that it can be
> done, but I'm just not the right man for the job without some
> assistance.
>
>
> What I've tried in Access 2.0 is to open a query in design view
> and select the "Query" item from the menubar. Then choose
> "SQL Specific" and "Pass-Through". Then, in the properties
> box for the query object, I invoke the builder button for the
> "ODBC Connect String" property. I'm then prompted to
> specify an SQL Data Source. That's where I lose it. I
> can't seem to input the parms (see prev post) needed.
>
> Error: ODBC call failed... [MicroSoft] [ODBC DLL] Datasource not
> found and no default ODBC driver specified.
>
> I've tried stuff like the following for the ODBCConnectStr Property...
> ODBC;DSN=ABCD;SERVER=123.456.789.10;UID=UserName;P WD=MyPass
> where ABCD is the dBase name on the remote server.
>
> Somehow, my syntax just isn't getting through. Should I be passingsure
> something else for the DNS and SERVER assignments? Where do
> I go from here?[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure about some of this, but . . . .

The DSN is the Data Source Name - have you created one on the PC the
application is running on? If you don't want to use a DSN you can go
DSN-less. Here's the part I'm not sure about - how to create a DSN
less connect string for dBase. In MS SQL Server a DSN-less connect
string would be like this:

ODBC;Driver={SQL Server};Server=123.456.789.10;UID=UserName;PWD=MyP ass

So I'd guess dBase DSN-less connect string would be like this:

ODBC;Driver={Microsoft dBase Driver};Server=123.456.789.10;UID=....
etc.

Look in the ODBC administrator for the names of the drivers available
on your PC. Hoepfully, any other PC that the Access app. runs on will
have the same db drivers.

I don't usually use the Builder wizards - I just type the string
straight into the ODBC Connect String property of the query.

The advantage of a DSN-less connection is you don't have to create a
DSN object for each PC that will use the application - as long as the
PC has access to the network that the db server is on the DSN-less
connection should work.

HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4dc04echKqOuFEgEQLTlwCgt2UJLUCHBkk1s8Y3e2IB/gUCiz8AoPDo
OFLfuWmSiVzZD0wlQ8iVKzLU
=mGqA
-----END PGP SIGNATURE-----

re: SQL syntax assistance on a Pass-Through query desparately needed...

On Sat, 11 Oct 2003 01:28:34 GMT, MGFoster <me@privacy.com> wrote:
[color=blue]
>The DSN is the Data Source Name - have you created one on the PC the
>application is running on? If you don't want to use a DSN you can go
>DSN-less. Here's the part I'm not sure about - how to create a DSN
>less connect string for dBase. In MS SQL Server a DSN-less connect
>string would be like this:
>
>ODBC;Driver={SQL Server};Server=123.456.789.10;UID=UserName;PWD=MyP ass
>
>So I'd guess dBase DSN-less connect string would be like this:
>
>ODBC;Driver={Microsoft dBase Driver};Server=123.456.789.10;UID=....[/color]

I tried MyODBC3.dll as the Driver= assignment value. Are the curly
brackets important? I tried with and without them - no luck. This is
the actual DLL file name. So, I thought "Maybe you don't use the
actual file nam... maybe the name of the user friendly name as its
known in Windows???" So,

I tried
ODBC;Driver=MySQL ODBC 3.51
Driver;SERVER=123.456.789.10;UID=UserName;PWD=MyPa ss

and I tried
ODBC;Driver={MySQL ODBC 3.51
Driver};SERVER=123.456.789.10;UID=UserName;PWD=MyP ass
(with the curly brackets)

- neither worked.

I have NO trouble with Access 97 getting to the remote server. I'm
sure Access 2.0 would do it if I can get around this connect string
thing.

re: SQL syntax assistance on a Pass-Through query desparately needed...

How can I verify that a Data Source Name has been set up on a PC?
Its a Windows XP box.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx


On Sat, 11 Oct 2003 01:28:34 GMT, MGFoster <me@privacy.com> wrote:
[color=blue]
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Not sure about some of this, but . . . .
>
>The DSN is the Data Source Name - have you created one on the PC the
>application is running on? If you don't want to use a DSN you can go
>DSN-less. Here's the part I'm not sure about - how to create a DSN
>less connect string for dBase. In MS SQL Server a DSN-less connect
>string would be like this:
>
>ODBC;Driver={SQL Server};Server=123.456.789.10;UID=UserName;PWD=MyP ass
>
>So I'd guess dBase DSN-less connect string would be like this:
>
>ODBC;Driver={Microsoft dBase Driver};Server=123.456.789.10;UID=....
>etc.
>
>Look in the ODBC administrator for the names of the drivers available
>on your PC. Hoepfully, any other PC that the Access app. runs on will
>have the same db drivers.
>
>I don't usually use the Builder wizards - I just type the string
>straight into the ODBC Connect String property of the query.
>
>The advantage of a DSN-less connection is you don't have to create a
>DSN object for each PC that will use the application - as long as the
>PC has access to the network that the db server is on the DSN-less
>connection should work.
>
>HTH,
>- --
>MGFoster:::mgf
>Oakland, CA (USA)[/color]

re: SQL syntax assistance on a Pass-Through query desparately needed...

MLH wrote:[color=blue]
> How can I verify that a Data Source Name has been set up on a PC?
> Its a Windows XP box.
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx
>
>
> On Sat, 11 Oct 2003 01:28:34 GMT, MGFoster <me@privacy.com> wrote:
>
>[color=green]
>>-----BEGIN PGP SIGNED MESSAGE-----
>>Hash: SHA1
>>
>>Not sure about some of this, but . . . .
>>
>>The DSN is the Data Source Name - have you created one on the PC the
>>application is running on? If you don't want to use a DSN you can go
>>DSN-less. Here's the part I'm not sure about - how to create a DSN
>>less connect string for dBase. In MS SQL Server a DSN-less connect
>>string would be like this:
>>
>>ODBC;Driver={SQL Server};Server=123.456.789.10;UID=UserName;PWD=MyP ass
>>
>>So I'd guess dBase DSN-less connect string would be like this:
>>
>>ODBC;Driver={Microsoft dBase Driver};Server=123.456.789.10;UID=....
>>etc.
>>
>>Look in the ODBC administrator for the names of the drivers available
>>on your PC. Hoepfully, any other PC that the Access app. runs on will
>>have the same db drivers.
>>
>>I don't usually use the Builder wizards - I just type the string
>>straight into the ODBC Connect String property of the query.
>>
>>The advantage of a DSN-less connection is you don't have to create a
>>DSN object for each PC that will use the application - as long as the
>>PC has access to the network that the db server is on the DSN-less
>>connection should work.
>>
>>HTH,
>>- --
>>MGFoster:::mgf
>>Oakland, CA (USA)[/color]
>
>[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To find the ODBC DSNs on Windows XP:

1. Click Start button
2. Click Settings - select Control Panel
3. Double-click "Administrative Tools"
4. Double-click "Data Sources (ODBC)" - the ODBC administrator dialog
box will appear.
5. Under the User, System of File DSN tabs look for the Data Source
Name you had in your original connect string "DSN=???".

Re: your other post - You can't use the name of the DLL that holds the
db engine driver - use the name of the mySQL driver as it is listed in
the ODBC administrator dialog "Drivers" tab. I always use the curly
brackets for the Driver= parameter - that's how I learned it.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4n8loechKqOuFEgEQJJEQCg3lLzMt25AN2b1/Xznv/aDca2mwoAoNJ3
xeXHm1Rq4sMxhpMdgXPAYCpv
=kcbI
-----END PGP SIGNATURE-----