How to avoid connection popup?

  • Thread starter Thread starter Hallgeir
  • Start date Start date
H

Hallgeir

I have an access application with linked odbc tables to a "ms sql server
2000" database.
Everytime we start the access app and try to use it the connection dialog
pops up an the users have to type in user name and password. When this is
done the access app workes fine until the next time we start it.
My questions is: Is there a way to read the necessary link information from
a given linked tabel and then use this information to refresh the connection
in a way so that the users don't have to see and relate to the connection
popup? And can someone give example or hints on how to write code to perform
such a task?

Thanks!
 
Hallgeir said:
I have an access application with linked odbc tables to a "ms sql server
2000" database.
Everytime we start the access app and try to use it the connection dialog
pops up an the users have to type in user name and password. When this is
done the access app workes fine until the next time we start it.
My questions is: Is there a way to read the necessary link information
from a given linked tabel and then use this information to refresh the
connection in a way so that the users don't have to see and relate to the
connection popup? And can someone give example or hints on how to write
code to perform such a task?

Thanks!
Click to expand...

The best way would be to use Wiindows integrated security - this is where
you have SQL logins which correspond to Windows user accounts. Since you
have already logged on to Windows, you do not need to log on again to SQL
Server. Ask the administrator to set this up for you.
If this is not possible, then there are other ways but these may pose a
security risk as you basically want to by-pass the login mechanism. If you
are not worried about this then you can save the password with the linked
table when you create the links.
 
"Justin Hoffman" wrote in message
If you are not worried about security then you can save the password with
the linked table when you create the links.

Hey
Thanks for the information. Security is not an issue in my case. So now I'am
wondering how I can save the password with the linked tables. I can't find
any option to save the password when i create the link. I create the link
from the menu, File, Get external data, Link table.

mvh
Hallgeir
 
Hallgeir said:
"Justin Hoffman" wrote in message
If you are not worried about security then you can save the password with
the linked table when you create the links.

Hey
Thanks for the information. Security is not an issue in my case. So now
I'am wondering how I can save the password with the linked tables. I can't
find any option to save the password when i create the link. I create the
link from the menu, File, Get external data, Link table.

mvh
Hallgeir
Click to expand...

Hi Hallgeir
There are lots of variations on this theme, but here is one. Paste all the
code into a new module, alter the two variables strConnect and strTableList
so they match your requirements. Then compile and save the module. You can
then type CTRL-G to get the immediate window and type ?RelinkTables() which
should return true.

It may seem strange to list the tables, but often the front end can link to
one or more back ends and not all the tables are required - so explicitly
listing them does give you more control.


Public Function ReLinkTables() As Boolean

On Error GoTo Err_Handler

Dim strConnect As String
Dim strTableList As String

strConnect = "ODBC;Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Uid=MyLogin;Pwd=MyPassword;"

strTableList = "tblOne;" & _
"tblTwo;" & _
"tblThree"

If DeleteLinks(strTableList) Then
If LinkTables(strTableList, strConnect) Then
ReLinkTables = True
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function



Private Function LinkTables(strTableList As String, strConnect As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTableName As String
Dim lngCount As Long

Set dbs = CurrentDb

astrTables = Split(strTableList, ";")

For lngCount = 0 To UBound(astrTables())

strTableName = astrTables(lngCount)

If Len(strTableName) > 0 Then

Set tdf = dbs.CreateTableDef(strTableName, dbAttachSavePWD)

tdf.Connect = strConnect

tdf.SourceTableName = strTableName

dbs.TableDefs.Append tdf

Set tdf = Nothing

End If

Next lngCount

LinkTables = True

Exit_Handler:

On Error Resume Next

If Not tdf Is Nothing Then
Set tdf = Nothing
End If

If Not tdf Is Nothing Then
Set dbs = Nothing
End If

Application.RefreshDatabaseWindow

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


Private Function DeleteLinks(strTableList As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTableName As String
Dim lngCount As Long
Dim blnError As Boolean

Set dbs = CurrentDb

astrTables = Split(strTableList, ";")

For lngCount = 0 To UBound(astrTables())

strTableName = astrTables(lngCount)

If Len(strTableName) > 0 Then

If TableExists(strTableName) Then

Set tdf = dbs.TableDefs(strTableName)

If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete tdf.Name
Else
' This is not a linked table
MsgBox "Cannot delete table '" & strTableName & "'",
vbExclamation
blnError = True
End If

Set tdf = Nothing

End If

End If

Next lngCount

If Not blnError Then
DeleteLinks = True
End If

Exit_Handler:

On Error Resume Next

dbs.TableDefs.Refresh

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Application.RefreshDatabaseWindow

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function TableExists(strTableName As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf

Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
 
"Justin Hoffman" wrote in message
Hi Hallgeir
There are lots of variations on this theme, but here is one. Paste all
the code into a new module, alter the two variables strConnect and
strTableList so they match your requirements. Then compile and save the
module. You can then type CTRL-G to get the immediate window and type
?RelinkTables() which should return true.

It may seem strange to list the tables, but often the front end can link
to one or more back ends and not all the tables are required - so
explicitly listing them does give you more control.
Click to expand...
Thank you Justin!
I tried your code and it worked just fine. This could be the solution of my
problems but there is still a small issue I have to solve. The linked tabels
in my access app can not have the same name as the tables in the sql server
database. By using two Tablelist variables (one with access app table names
and one with sql server database name) I manage to delete the existing links
and reconnect the tables with the names from the sql server database, but I
can't figur out how I can give the reconnected tables the name I want to
give them (the same name that I store in my first Tablelist variabel).

mvh
Hallgeir
 
Hallgeir said:
Thank you Justin!
I tried your code and it worked just fine. This could be the solution of
my problems but there is still a small issue I have to solve. The linked
tabels in my access app can not have the same name as the tables in the
sql server database. By using two Tablelist variables (one with access app
table names and one with sql server database name) I manage to delete the
existing links and reconnect the tables with the names from the sql server
database, but I can't figur out how I can give the reconnected tables the
name I want to give them (the same name that I store in my first Tablelist
variabel).

mvh
Hallgeir
Click to expand...




Hi Hallgeir
Perhaps you could define your list as pairs of table names. Here the
semi-colon( ; ) separates the list into pairs and the pipe( | ) separates
the pair into two halves. Note that this means I have had to alter both the
LinkTables and DeleteLinks functions.
Would this be OK for you?



Public Function ReLinkTables() As Boolean

On Error GoTo Err_Handler

Dim strConnect As String
Dim strTableList As String

strConnect = "ODBC;Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Uid=MyLogin;Pwd=MyPassword;"

' Table list is in pairs like "SQL_Server|Link_Table;"
strTableList = "dbo.Customer|tblCustomers;" & _
"dbo.Product|tblProducts;" & _
"dbo.Order|tblOrders"

If DeleteLinks(strTableList) Then
If LinkTables(strTableList, strConnect) Then
ReLinkTables = True
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


Private Function LinkTables(strTableList As String, strConnect As String) As
Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strSqlServerTable As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long

Set dbs = CurrentDb

astrTables = Split(strTableList, ";")

For lngCount = 0 To UBound(astrTables())

strTwoTables = astrTables(lngCount)

lngPosition = InStr(strTwoTables, "|")

If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then

strSqlServerTable = Mid$(strTwoTables, 1, lngPosition - 1)

strLinkTable = Mid$(strTwoTables, lngPosition + 1)

Set tdf = dbs.CreateTableDef(strLinkTable, dbAttachSavePWD)

tdf.Connect = strConnect

tdf.SourceTableName = strSqlServerTable

dbs.TableDefs.Append tdf

Set tdf = Nothing

End If

Next lngCount

LinkTables = True

Exit_Handler:

On Error Resume Next

If Not tdf Is Nothing Then
Set tdf = Nothing
End If

If Not tdf Is Nothing Then
Set dbs = Nothing
End If

Application.RefreshDatabaseWindow

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


Private Function DeleteLinks(strTableList As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Dim blnError As Boolean

Set dbs = CurrentDb

astrTables = Split(strTableList, ";")

For lngCount = 0 To UBound(astrTables())

strTwoTables = astrTables(lngCount)

lngPosition = InStr(strTwoTables, "|")

If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then

strLinkTable = Mid$(strTwoTables, lngPosition + 1)

If TableExists(strLinkTable) Then

Set tdf = dbs.TableDefs(strLinkTable)

If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete tdf.Name
Else
' This is not a linked table
MsgBox "Cannot delete table '" & strLinkTable & "'",
vbExclamation
blnError = True
End If

Set tdf = Nothing

End If

End If

Next lngCount

If Not blnError Then
DeleteLinks = True
End If

Exit_Handler:

On Error Resume Next

dbs.TableDefs.Refresh

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Application.RefreshDatabaseWindow

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function TableExists(strTableName As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf

Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
 
Thank you, Justin
Your code makes wonder. It solved my problem and I learned some nice
programming moves.

mvh
Hallgeir
 
Back
Top