[RESOLVED] Linked tables in MS Access

[sebs]

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

[si_the_geek]

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.

[RobDog888]

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.

[sebs]

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

[sebs]

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

[si_the_geek]

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.

[sebs]

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:

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

[si_the_geek]

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

[sebs]

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

[sebs]

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:

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.