Re: dsn-less connection

Prompt the user for their user name and password, store them in variables
myUsername and myPassword and replace the line:

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" &
DatabaseName & ";SERVER=" & ServerName & ";UID=;PWD=;"

with

tdfCurrent.Connect = "ODBC;DRIVER={SQL Server};" & _
"DATABASE=" & DatabaseName & ";SERVER=" & _
ServerName & ";UID=" & myUsername & ";PWD=" & _
myPassword & ";"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Noone" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:eJFY3BoaGHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
Hello. I was wondering if anyone would know of how to modify the below
code that I found on the web if possible which converts existing dsn link
tables from ms access to sql server to dsn-less but have it prompt the
user to supply their individual sql login id and password when they go in
and open a table? When I run this code I notice it saves the converted
table link as a trusted connection which instead I want each user who
would be accessing it to supply their own sql login credentials.

Thanks in advance.

J


Sub FixConnections(ServerName As String, DatabaseName As String)
' Looks for any TableDef objects that have a connection string, and
' changes it to DSN-less


Dim dbCurrent As Database
Dim intLoop As Integer
Dim intToChange As Integer
Dim lngAttributes() As Long
Dim strSourceTableName() As String
Dim strTableName() As String
Dim tdfCurrent As TableDef


intToChange = 0


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then


' This is a linked table: save its characteristics.
' If you have linked tables other than to SQL Server, you'd put
' logic in here to ignore those tables not linked to SQL Server.


ReDim Preserve lngAttributes(intToChange)
ReDim Preserve strSourceTableName(intToChange)
ReDim Preserve strTableName(intToChange)
lngAttributes(intToChange) = tdfCurrent.Attributes
strTableName(intToChange) = tdfCurrent.Name
strSourceTableName(intToChange) = tdfCurrent.SourceTableName
intToChange = intToChange + 1
End If
Next


' Go through the array of linked table characteristics.
' Delete the table, then relink, using a DSN-less Connection.


For intLoop = 0 To intToChange - 1
dbCurrent.TableDefs.Delete strTableName(intLoop)
Set tdfCurrent = dbCurrent.CreateTableDef(strTableName(intLoop))
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" &
DatabaseName & ";SERVER=" & ServerName & ";UID=;PWD=;"
tdfCurrent.SourceTableName = strSourceTableName(intLoop)
dbCurrent.TableDefs.Append tdfCurrent
Next


Set tdfCurrent = Nothing
Set dbCurrent = Nothing


End Sub




.

Relevant Pages

  • RE: How to populate VBA Variables from ADODB.Connection Results?
    ... y As Integer 'Incremental counters to populate ... 'Find SQL Server data for the active user. ... "GetUserNameA" (ByVal lpBuffer As String, ... Dim UserName As String ...
    (microsoft.public.excel.programming)
  • Error Trap Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.security)
  • Help with Error Trapping Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: using VBA to insert records from access database to sql server
    ... Brendan and Tim, ... insert records from Access table to SQL server table with one error which I ... Dim oConn As ADODB.Connection ... Dim strInsert As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with MS Access Error Trap
    ... your access code and is not a SQL Server issue per se. ... > Dim rs1 As DAO.Recordset ... > Dim strLinkName As String ... > ' Now remove previous link info ...
    (microsoft.public.sqlserver.programming)
Loading