[RESOLVED] Linked tables in MS Access

Hey,

I have a linked(from MS SQL Server 2K5) table in MS Access 2K and I cannot query it because it is asking me for the password everytime.

I do not see an option to save the password in my ODBC connection nor when I link the table.

Please tell me that there's something to do about this ?

Thanks

I can't remember what stage of the process it is, but the option is there as part of the linking process - I'm pretty sure it is a tick-box at the bottom of one of the screens that you go thru.

Yes, there should be the option when you create the link. I believe its a checkbox that you need to check.

It may also be in your ODBC datasource connection that you specified during the linking.

Geez, that's what I've been reading on the net, but I double triple checked and I cannot see that damm option :(

I have read somewhere that you could store the password in the tabledef of the linked table, does that make sense ??

Thanks for your help :thumb:

Small update !

I created my own dsn, now when I select it in the dsn list, I do not get prompt for my password, but as soon as I close MS Access and reopen it, I get prompt for it again.

If I look in MSysObjects and I look at the Connect field, it saves everything BUT the password.

Where can I save it ? Can I do this with Access 2000 ?

Thanks

I think you need to use the "Linked Table Manager" (under "Tools"->"Database Utilities"), rather than simply selecting "Link tables..".

Unfortunately I cant test as I haven't got that installed at the moment, and dont have permissions with this user account to install it.

Nah, it asked me for the password and when I put it in, it just refreshed the table.

I need to be able to query the database thru asp.

I found this piece of code:

VB Code:

Option Compare Database
 
Public Sub ChangeConnection(ByVal TableName As String, ByVal ConnectionInfo As String)
   'Dim db As DAO.Database
   'Dim tdf As DAO.TableDef
 
   Set db = CurrentDb
   Set tdf = db.TableDefs(TableName)
 
   'Change the connect to what you need for your connection.
   MsgBox tdf.connect
   tdf.connect = ConnectionInfo
   MsgBox tdf.connect
   
   Set tdf = Nothing
   db.Close
   Set db = Nothing
End Sub
 
Sub test()
    Call ChangeConnection("ve_forms", "connection string here")
End Sub

and I can see the new connection string on my second msgbox, but as soon as the code is doen running, it goes back to the previous string :(

Have you tried removing the linked table(s) first, then running Linked Table Manager?

Yeah, I just tried, it says that there's no linked tables :(

FINALLY !

I figured it out, I used some of the code I had to backup our sql database.

Anyway, the trick is to create the link from code:

VB Code:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL Server;UID=**;PWD=**;SERVER=**;APP=Microsoft Data Access Components;", acTable, TableName, TableName, False, True

and it store the full connection string and when I re-open my db, I do not have to enter the password !

I am so happy right now, thanks for your help si_the_geek and RobDog888.