ODBC Connect String

[Guest]

I wanted to include the OBDC connection string in my select query. In the
Query Properties, I've entered the connection string in the Source Connect
Str field, so my SQL now looks like this:

SELECT DISTINCT MCMCU, MCLDM
FROM PRODDTA_F0006 IN '' [ODBC;DSN=Harvest
Production;UID=SELECT_USER;PWD=SELJDE;DBQ=AGJD1PDD];

When I run the query, I get an error message telling me the Jet database
engine cannot find the table or query PRODDTA_F0006. If I run the query
without the connection string, it prompts me for the OBDC password, and
everything runs fine. I was hoping to include the password in the string so
I didn't have to type it in all the time. Help!

 

[Arvin Meyer]

My suggestion would be to create a new DSN with a saved password and use it
for those queries. It is also less of a security risk than exposing a
password in the query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I know how to set up a DSN, but I don't see an option to save the password.
Is this something that can be done at the "user" level, or do I need
Administrative access?

My suggestion would be to create a new DSN with a saved password and use it
for those queries. It is also less of a security risk than exposing a
password in the query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I wanted to include the OBDC connection string in my select query. In the
Query Properties, I've entered the connection string in the Source Connect
Str field, so my SQL now looks like this:

SELECT DISTINCT MCMCU, MCLDM
FROM PRODDTA_F0006 IN '' [ODBC;DSN=Harvest
Production;UID=SELECT_USER;PWD=SELJDE;DBQ=AGJD1PDD];

When I run the query, I get an error message telling me the Jet database
engine cannot find the table or query PRODDTA_F0006. If I run the query
without the connection string, it prompts me for the OBDC password, and
everything runs fine. I was hoping to include the password in the string so
I didn't have to type it in all the time. Help!

 

[Arvin Meyer]

Actually, now that I think about it, saving the password is an option while
you are linking the tables. That's not what you seem to be wanting. So, my
next suggestion would be to create and link the table, saving the password.
Then rename the table to USysPRODDTA_F0006. That will hide the table. Now
write your query as:

SELECT DISTINCT MCMCU, MCLDM
FROM USysPRODDTA_F0006;

The query will work in code or you can also hide it and bind to it by name.
The prefix USys will hide user objects, automatically, without setting any
other property.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I know how to set up a DSN, but I don't see an option to save the password.
Is this something that can be done at the "user" level, or do I need
Administrative access?

My suggestion would be to create a new DSN with a saved password and use it
for those queries. It is also less of a security risk than exposing a
password in the query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I wanted to include the OBDC connection string in my select query. In the
Query Properties, I've entered the connection string in the Source Connect
Str field, so my SQL now looks like this:

SELECT DISTINCT MCMCU, MCLDM
FROM PRODDTA_F0006 IN '' [ODBC;DSN=Harvest
Production;UID=SELECT_USER;PWD=SELJDE;DBQ=AGJD1PDD];

When I run the query, I get an error message telling me the Jet database
engine cannot find the table or query PRODDTA_F0006. If I run the query
without the connection string, it prompts me for the OBDC password, and
everything runs fine. I was hoping to include the password in the

string
so

I didn't have to type it in all the time. Help!

 

[Guest]

So how do you actually go about saving the password while creating the link
to the table?

Actually, now that I think about it, saving the password is an option while
you are linking the tables. That's not what you seem to be wanting. So, my
next suggestion would be to create and link the table, saving the password.
Then rename the table to USysPRODDTA_F0006. That will hide the table. Now
write your query as:

SELECT DISTINCT MCMCU, MCLDM
FROM USysPRODDTA_F0006;

The query will work in code or you can also hide it and bind to it by name.
The prefix USys will hide user objects, automatically, without setting any
other property.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I know how to set up a DSN, but I don't see an option to save the password.
Is this something that can be done at the "user" level, or do I need
Administrative access?

My suggestion would be to create a new DSN with a saved password and use it
for those queries. It is also less of a security risk than exposing a
password in the query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I wanted to include the OBDC connection string in my select query. In the
Query Properties, I've entered the connection string in the Source Connect
Str field, so my SQL now looks like this:

SELECT DISTINCT MCMCU, MCLDM
FROM PRODDTA_F0006 IN '' [ODBC;DSN=Harvest
Production;UID=SELECT_USER;PWD=SELJDE;DBQ=AGJD1PDD];

When I run the query, I get an error message telling me the Jet database
engine cannot find the table or query PRODDTA_F0006. If I run the query
without the connection string, it prompts me for the OBDC password, and
everything runs fine. I was hoping to include the password in the string
so
I didn't have to type it in all the time. Help!

 

[Van T. Dinh]

In Windows XP, I am fairly sure "Restricted Users" can't set up DSN. You
need to use "Normal Users" or "Administrators" access on the PC.

The UserName / Password is usually the second screen (in my case, MS-SQL
Server 2000) of the DSN set-up wizard but it may depends on the back-end
engine.

--
HTH
Van T. Dinh
MVP (Access)

 

[Arvin Meyer]

When you pick the ODBC choice from the Link dialog in Access, it will ask
for Username and Password for that database. You need to supply that. Then
you are presented with the link dialog box, in the lower right-hand corner
of which is a checkbox to save the password. If you check it, you will not
be prompted again unless your user is deleted from the server database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

So how do you actually go about saving the password while creating the link
to the table?

Actually, now that I think about it, saving the password is an option while
you are linking the tables. That's not what you seem to be wanting. So, my
next suggestion would be to create and link the table, saving the password.
Then rename the table to USysPRODDTA_F0006. That will hide the table. Now
write your query as:

SELECT DISTINCT MCMCU, MCLDM
FROM USysPRODDTA_F0006;

The query will work in code or you can also hide it and bind to it by name.
The prefix USys will hide user objects, automatically, without setting any
other property.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I know how to set up a DSN, but I don't see an option to save the password.
Is this something that can be done at the "user" level, or do I need
Administrative access?

:

My suggestion would be to create a new DSN with a saved password and

use
it

for those queries. It is also less of a security risk than exposing a
password in the query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I wanted to include the OBDC connection string in my select query.

In
the

Query Properties, I've entered the connection string in the Source Connect
Str field, so my SQL now looks like this:

SELECT DISTINCT MCMCU, MCLDM
FROM PRODDTA_F0006 IN '' [ODBC;DSN=Harvest
Production;UID=SELECT_USER;PWD=SELJDE;DBQ=AGJD1PDD];

When I run the query, I get an error message telling me the Jet database
engine cannot find the table or query PRODDTA_F0006. If I run the query
without the connection string, it prompts me for the OBDC

password,
and

everything runs fine. I was hoping to include the password in the string
so
I didn't have to type it in all the time. Help!