Results 1 to 8 of 8
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    Trying to connect through DAO but getting "Run-time error '3043': Your network access interrupted"


    I am trying to learn DAO and I can't figure out what I am doing wrong.
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Sub runQuery()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = OpenDatabase("DSN=VitaServer;UID=Username;PWD=Password;")
    Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
    
    
    If Not rs.EOF Then rs.MoveFirst
    Do Until rs.EOF
        Debug.Print rs!FirstName
        rs.MoveNext
    Loop
    
    
    db.Close
    rs.Close
    
    
    
    
    End Sub
    Originally I had it set as
    Code:
    OpenDatabase("VitaServer", false, false, "UID=Username;PWD=Password")
    but that was giving the same error code but a different message.

    I have checked and my ODBC connection has "VitaServer" as the DSN.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you are already IN the database. Theres no reason to use DAO.
    if the table is attached,
    just do:

    set rs= currentdb.openrecordset("sql....")

  3. #3
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by ranman256 View Post
    you are already IN the database. Theres no reason to use DAO.
    if the table is attached,
    just do:

    set rs= currentdb.openrecordset("sql....")
    I am trying to link tables to use on a form. I did so through new data source > SQL server and I am trying to make it so there is no prompt to log in but I am still getting the prompt even with said code as if I am not in the database.

  4. #4
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Vita View Post
    I am trying to link tables to use on a form. I did so through new data source > SQL server and I am trying to make it so there is no prompt to log in but I am still getting the prompt even with said code as if I am not in the database.
    I still am trying to figure this out. I have some tables linked through an sql server as stated above and I am trying to use DAO to get rid of the prompt as I am going to need to set this up on several company computers for several different people to use. I know its dumb but they can't know the password so I need it to be handled in the background.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your connection string isn't correct.
    Type the following into the immediate window replacing the table name with a linked table to see what you should have:

    ? Currentdb.TableDefs("YourLinkedTablename").Connect
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Minty View Post
    Your connection string isn't correct.
    Type the following into the immediate window replacing the table name with a linked table to see what you should have:

    ? Currentdb.TableDefs("YourLinkedTablename").Connect
    I tried this spelling it 3 different ways "dbo.Employees", "dbo_Employees", and "Employees" and Each time I got "Run-time error '3265': Item not found in this collection."

    I know the table exists because I linked it through the MS Access UI so its under my linked tables as "dbo_Employees".

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Open the system table msysObjects and look at the Connect strings for the linked SQL tables (you might need to go to File\Options\Current Database\Navigation Options and turn on View System Objects to view that table).
    Here is what I use to refresh the links for SQL tables; the connection string is saved in a field in a local front-end table (you can make that a custom system table by using the prefix "usys" and will be hidden for the users):
    Code:
    Public Function vcLinkTableDefs()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnectionString As String
    
    
    On Error Resume Next
    
    
    
    
      Set dbs = CurrentDb()
    
    
      ' Loop through TableDefs collection, only processing
      ' the table if it already has a Connection property.
      ' (all other tables are local ... not linked)
    
    
      For Each tdf In dbs.TableDefs
        If tdf.Connect <> "" Then
        strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
           If tdf.Connect <> strNewConnectionString Then
               tdf.Connect = strNewConnectionString
               tdf.RefreshLink
           End If       
        End If
      Next
    
    
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    Open the system table msysObjects and look at the Connect strings for the linked SQL tables (you might need to go to File\Options\Current Database\Navigation Options and turn on View System Objects to view that table).
    Here is what I use to refresh the links for SQL tables; the connection string is saved in a field in a local front-end table (you can make that a custom system table by using the prefix "usys" and will be hidden for the users):
    Code:
    Public Function vcLinkTableDefs()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnectionString As String
    
    
    On Error Resume Next
    
    
    
    
      Set dbs = CurrentDb()
    
    
      ' Loop through TableDefs collection, only processing
      ' the table if it already has a Connection property.
      ' (all other tables are local ... not linked)
    
    
      For Each tdf In dbs.TableDefs
        If tdf.Connect <> "" Then
        strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
           If tdf.Connect <> strNewConnectionString Then
               tdf.Connect = strNewConnectionString
               tdf.RefreshLink
           End If       
        End If
      Next
    
    
    End Function
    Cheers,
    I ended up finding the correct connection string after some digging. I had the wrong driver name in the ODBC field and found out some of the options in my odbc connection were set wrong.
    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-28-2015, 01:45 AM
  2. Replies: 3
    Last Post: 08-20-2015, 10:31 AM
  3. Replies: 1
    Last Post: 03-14-2013, 12:39 PM
  4. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  5. Network Error 3043
    By khalid in forum Programming
    Replies: 10
    Last Post: 05-15-2011, 02:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums