Access 2000 Db with ODBC Connection to SQL Db

[PaulG111]

Hello All,

I have a problem with an Access 2000 front end which needs to take data from an Access 2000 Db and pass it to a remote SQL Db.

I added the SQL tables by creating an ODBC Data Source and then using it to link SQL to the Access 2000 front end. This works fine and I can read and write data to the SQL tables fine.

However, the the data source employs a user ID and a password to connect. When I save the Db the linked tables remain in the Db window ok but each time I try to access the sql tables I get asked for the login password for the connection.

I cannot seem to get the data source or the link table properties to save/retain the password between each time I use the Db?

Is there a way of doing this or do I need to connect to the SQL Db in a different way? Or do I need to just use VBA and a connection string with the password embedded in that?

Any help you can provide is greatly appreciated.

Regards,

Paul

"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[chiefman]

I'm not totally sure, but I think that a VBA connection string with the username and password might be your best option. You could even store the login information in a variable if there are different users and then pass that to the connection string.

 

[PaulG111]

Thanks chiefman,

I'm hoping to keep the VBA approach as the last resort. All I need is a stable connection to a couple of linked sql tables which I can just run some queries against without having to re-enter the password each time the front end starts up.

I know the VBA approach will be much more flexible but the stable link would be a lot easier for me.

If there was just a way of saving the password with the data source or the table link I would be a happy chappy.

Regards,

PaulG



"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[PHV]

You didn't tick the Remember password checkbox in the table link manager ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulG111]

Hi PHV,

Is the Linked Table Manager the one found in Tools/Database Utilities on the Access 2000 main menu toolbar?

If so, I can't see a remeber password checkbox there.
I also can't see one in the link table dialogue itself.
If you can point me to it I would be very grateful.

Regards,

PaulG111

"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[PHV]

menu Files -> External data -> Link tables ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulG111]

Hi PHV,

Yes, External Data->Link Tables is what I used to link the SQL table to the fonr end but I can't see any checkbox for remember password during the configuration of the link.

Can you actually see the checkbox in Access 2000 when configuring the table link?

Regards,

PaulG

"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[PHV]

I see it in ac2k3 after the choice of the ODBC DSN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulG111]

Thanks PHV,

FINALLY! I see it.

Thanks for your perseverance with me - I must be going blind in my old age!


Very much appreciated.

Regards,

PaulG.



"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it

 

[lradon]

Hi Paul,

I am trying to do what you are doing using Access as a front end and SQL tables as the backend.

********************************************************
"I have a problem with an Access 2000 front end which needs to take data from an Access 2000 Db and pass it to a remote SQL Db.

I added the SQL tables by creating an ODBC Data Source and then using it to link SQL to the Access 2000 front end. This works fine and I can read and write data to the SQL tables fine."
***********************************************************

However, I can't write/edit the table through the ODBC link and I was wondering if you could help me configure the odbc link and/or SQL login to allow users to do this. I am not too knowledgeable regarding this portion of SQL.

Thanks, Laura

 

[PaulG111]

Hi Laura,

I am afraid that my SQL Server knowledge is limited.
I was able to create a data connection using the ODBC driver for SQL Server Db. This links some tables to my Access 2000 front end and I can now read and write data to the tables. However, this connection does not allow me to modify the table design/schema. I do not know whether this is by virtue of the nature of the connection or as a result of the limited permissions granted by my login.

My Access front end connects to the SQL Db server purely via an IP address specification and the default SQL port number. The version of the ODBC driver employed does have an impact however on how successfully you can connect to the back end Db. My original driver was for SQL Server 7 and earlier and did not connect successfully to the SQL 2000 Db.

With regard to configuring the SQL Db backend to accept an ODBC connection I’m afraid I cannot help you as this is beyond my knowledge. However, I am sure that a kind expert on this forum should be able to give you some pointers.

Wishing you success,

Paul


"God is a DJ
Life is a dance floor
You get what you're given
It's all how you use it