Loop not moving to next record in recordset

Posted on 2006-05-24
Last Modified: 2012-05-05
I have created a loop statement that is suppose to go thru a table list of TABLES and pass the table name in the ODBC connection string.

I have two problems -

It is only passing the first item in the list

2nd - the ODBC connection part of my Transfer database has a popup that will need to be manually updated -

I am trying to fully automate this process.

cross ref #


Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
        fOSUserName = vbNullString
    End If
End Function
'******************** Code End **************************
' Get_Latest_Metrica_Data_From_MTR2
' Will automatically update/replace the data weekly -
' to be run @ 3am evert Wednesday
Function Get_Latest_Metrica_Data_From_MTR2()
On Error GoTo Get_Latest_Metrica_Data_From_MTR2_Err
Dim nTable As String
Dim DB As Database
Dim RS As Recordset
Dim strSql As String
'SetWarnings = False

strSql = "SELECT Name" & _
        " FROM tblListActiveImportTables"

Set DB = currentdb()                'Use the current database
Set RS = DB.OpenRecordset(strSql)      'actually open the recordset

    Do While RS.EOF = False
        nTable = RS.Fields("Name")
        On Error Resume Next
        If IsObject(currentdb.TableDefs(nTable)) Then
            DoCmd.DeleteObject acTable, nTable
        End If
        DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=MTR2;UID=" & fOSUserName & ";PWD=" & fOSUserName & "; & DBQ=MTR2 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;;TABLE=" & Chr(34) & nTable & Chr(34) & ", acTable, " & Chr(34) & nTable & Chr(34) & "," & Chr(34) & nTable & Chr(34) & ", False"
        SendKeys "~"

    Exit Function

  MsgBox Error$

   Resume Get_Latest_Metrica_Data_From_MTR2_Exit

End Function
Question by:Karen Schaefer
    1 Comment
    LVL 19

    Accepted Solution

    Try ..



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    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

    10 Experts available now in Live!

    Get 1:1 Help Now