Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: DSN-Less connection code gives error 3011 cannot find object

    I am running the following function to roll through a table list and refresh links that I have to a SQL backend. I am getting Error 3011 which states:
    Any help yould be appreciated. I am using Access 2007

    Public Function fnLinkSQL()

    Dim myDB As Database, tdf As TableDef
    Set myDB = CurrentDb
    Dim rst As Recordset, rstDB As Recordset, strMyDb As String

    Dim strServerName As String
    On Error GoTo fnLinkSQL_Error

    Set rstDB = CurrentDb.OpenRecordset("SELECT tblDataSource.*, Left([tname],4) AS myfilter " _
    & "FROM tblDataSource " _
    & "WHERE (Left([tname],4)='dbo_')" _
    , dbOpenDynaset, dbSeeChanges)

    If Not rstDB.EOF Then
    rstDB.MoveFirst
    Do Until rstDB.EOF
    strTableName = rstDB!tname
    strServerName = rstDB!tserver
    Dim strCN_String As String
    Set tdf = myDB.TableDefs(strTableName) ' This is the way to fix the issue of the 1st procedure. Miracle!

    tdf.Connect = "DRIVER=SQL Server Native Client 10.0;" _
    & "SERVER=" & strServerName _
    & ";DATABASE=" & strDatabaseName _
    & ";Trusted_Connection=Yes"
    RemoveInvalidRelationships "WSSLink"
    tdf.RefreshLink
    rstDB.MoveNext
    Loop
    End If
    fnLinkSQL_Exit:
    Exit Function

    fnLinkSQL_Error:
    On Error Resume Next
    End Function
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Try disambiguating your variables:
    Dim myDB As DAO.Database, tdf As DAO.TableDef
    Set myDB = CurrentDb
    Dim rst As DAO.Recordset, rstDB As DAO.Recordset, strMyDb As String
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •