Solved

Re-Linking tables causes ODBC login-why??

Posted on 2006-06-22
17
273 Views
Last Modified: 2008-02-01
I'm working in Access 2003 using a DSN file to link to SQL Server 2005. I've been updating tables in SQL Server, so I then have to delete the linked table from the Access .mdb file and then re-link it so that it sees the changes that I've made (otherwise, the Access file won't see new columns that I've added).

Anyways,  I go through this process and things work fine, but when I open the Access .mdb, it pops up a login screen and I have to type in a password before the file opens. And if I use the old .mdb file (pre table re-linking), it just opens up without asking for the password. This is frustrating because my end-users aren't used to having to enter a password every time they open up the database. So...

1) Why does this occur?
2) How can I get around this--make the .mdb file open up WITHOUT it asking for a login after re-linking tables
3) Am I going about this the right way? If edits are made to a table in SQL Server, do you have to delete the linked table in Access and then re-import/link the table to see the updates? This kind of sucks, because in Access,  you can only delete one table at a time and in this database there are many many tables.
4) Is there a way to update the links of ALL the linked tables in the .mdb at once--I already tried the Linked Table Manager, but for some reason, NONE of my database logins would work (even sa). So, I end up deleting the tables using VB code, and then I re-import them by going to File>Link Existing Data. But then, that damn login pops up every time...arrrgh!

thanks guys!
goldy
0
Comment
Question by:goldylamont
  • 6
  • 6
  • 5
17 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16959059
what are u using to relink the tables
that code may have the option for password, did u check?

0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 350 total points
ID: 16959068
Did you tell it to save the password in the link?

No don't delete, you can right click on the table, choose Refresh Links (Memory may be failing here - but its the bottom menu item), then select the tables you want to refresh link and click OK. You can also code this to happen using VBA.

Password shouldn't appear if your using Windows Authentication on SQL, and if it's SQL authentication, then a little tickbox when you use the ODBC wizard to link should solve this.

Kelvin
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 total points
ID: 16959078
Have u tried refreshing the link?

Public Function RefreshLinks()

    Dim tbl As DAO.tableDef
    For Each tbl In DBEngine(0)(0).TableDefs
        If Len(tbl.Connect) > 0 Then
            tbl.RefreshLink
        End If
    Next

End Sub


the above refreshes all links


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16959101
when u link tables, are u using DoCmd.TransferDatabase?

e.g.

DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DATABASE=YourDB;YourODBCConectionString", acTable, tblname, tblname, False, True

the last parameter True is StoreLogin, I don't know if that would do the trick



0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 350 total points
ID: 16959106
For ODBC you want to determine its ODBC for this. You need ADOX (part of MDAC)

For instance

For Each tbl In cat.Tables
    With tbl
        'Only process linked ODBC tables
        If .Type = "PASS-THROUGH" Then
        fLink = LinkTableADOX(strLinkName:=.Name, strTableName:=.Properties("Jet OLEDB:Remote Table Name"), strDSNname:=strDSN, strServerName:=strServer, strDBName:=strSQLDB)
        End If
    End With
Next tbl

Public Function LinkTableADOX(strLinkName As String, strTableName As String, strDSNname As String, strServerName As String, strDBName As String) As Boolean

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

On Error Resume Next
    'Point the catalog to the current database
    cat.ActiveConnection = CurrentProject.Connection
   
    'If the link already exists, delete it
    Set tbl = cat.Tables(strLinkName)
    If Err = 0 Then
        cat.Tables.Delete strLinkName
    Else
        Err = 0
    End If
   
    'Set the name and parent catalog for the link
    tbl.Name = strLinkName
    Set tbl.ParentCatalog = cat
   
    'set the properties to create the link
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Description=TRMP Database;DRIVER=SQL Server;SERVER=" & strServerName & ";Database=" & strDBName & ";DSN=" & strDSNname & ";Integrated Security = True"
    tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName
    'Debug.Print "ODBC;Description=TAM Databases;DRIVER=SQL Server;SERVER=" & strServerName & ";UID=TAM_ClientReportingDBO;PWD=i3P21AQ;Database=" & strDBName & ";DSN=" & strDSNname & ";Integrated Security = True"
   
    'Append the table to the collection
    cat.Tables.Append tbl
   
    tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Description=TRMP Database;DRIVER=SQL Server;SERVER=" & strServerName & ";Database=" & strDBName & ";DSN=" & strDSNname & ";Integrated Security = True"
   
    Set cat = Nothing
    Set tbl = Nothing
   
    LinkTableADOX = (Err = 0)
   
End Function
 This will relink with Windows Authentication
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 16959121
The debug print line commented out is the code for a SQL Server Authentication
0
 

Author Comment

by:goldylamont
ID: 16959196
I'm using SQL Server Authentication. I know Windows Authentication is preferred, but i'm just taking on this company/project and I don't feel comfortable enough with Windows Authentication yet to transfer all the users over to it, although I'd like to.

So, going back to my original issue--I'm NOT doing anything in VB to link the tables (at least not by hand). Sorry if I didn't make that clear. I will definitely look into the code examples you posted though. However, all the linking/unlinking that I'm doing is using the Access GUI--either I go to File>Get External Data>Link Tables, or I go to Database Tools>Linked Table Manager.

I know that Linked Table Manager "should" take care of this for me. But it won't work because it won't recognize any login names, even the sa login name. When I go to File>...Link Tables, it works with all the logins. I go there; it opens up a dialogue box for me to pick a file to import/link from; I change the file type from .mdb to ODBC connection; then I get the dialogue that lists all of the DSN files that already exist; I select the correct one and the tables are re-linked.

The other thing is, the DSN file works fine for other Access .mdb files that use it and there's no login popup. It's only after I relink that I get the popup when opening. It's specific to which .mdb file I'm using.

hope this clarifies some. you guys rock man. keep up the dialogue and thanks so much!
goldy
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 350 total points
ID: 16959231
When you choose the dsn, you are normally prompted for the the tables to relink - it is at this point there is a tick box to save the password in the link. Does it also prompt you for the password?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:goldylamont
ID: 16959232
uuuh, also, where would i put said bad-ass-linking-code that you've so graciously provided if i decide to go that route? what do i do with it so that the end user just double clicks the file and then it opens with no hassles. this isn't to say that i want to abandon just using the Access GUI to resolve the problem in the short term. I need the quickest resolution at this point, and then i will go back and implement the best.

thx again,
goldy
0
 

Author Comment

by:goldylamont
ID: 16959246
kelvinsparks--no, it does not prompt me. after I choose the DSN from the dialogue window, it just pops up another window where i pick out the tables from SQL that i want to link.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 16959264
The first bit (above of the Function) would be in a form and use a command button to fire. The rest I usually stick in a Code Module. I also have the code for relinking pass through queries if you have them. Need DAO for that - mADO might work, but I'm still in the dark ages
0
 

Author Comment

by:goldylamont
ID: 16959308
i'm going to sleep for 120 minutes before i have to go to work again....but i always PAQ my questions and the points keep flowin baby. i'm so happy to get responses so soon, but i need sleep. i just didn't want you guys to think i would leave you hanging. as soon as i can, i will try out some of this stuff. but, what i really need is a way to do this without having to affect the end user in the least. they should just be able to open the Access file and it works...and it still works this way for the mdb's that i haven't touched with my ghetto-linking technique so i think there's some way around this snafu i need sleep and i don't recommend drinking Sparks Plus at 5:40am
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 total points
ID: 16959320
How do u relink at the moment, is it a manual job?

If u do it manually, note there is a Save Password checkbox, did u check that?

If u do it in code, what code do u use
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 16959326
I'm off to bed too, but its 12.40 am a day ahead of you here (kiwi land)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16959331
Dont know what kind of drink Sparks Plus
but at that time in the morning, Ive had Barcardi (obviously tailing off from a long night)

Can understand your sleep, but just 2hrs? wow u don't need much sleep eh
0
 

Author Comment

by:goldylamont
ID: 17118873
i'm such a dork. the problem WAS the checkbox in the popup window when linking the table. There are a couple of popups though and i was looking at the wrong one in the wrong place. i think i just needed to step away and get back. i gave points for the code submitted though because i think that it's kick ass and may use it in the future. thanks so much guys.

goldy
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17119642
No probs. Your sorted, thats the main thing
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 24
Access Date Query 28 29
Splitting out Data 14 27
Update a field with 3 characters from another field 3 19
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now