Loop not moving to next record in recordset

Posted on 2006-05-24
Medium Priority
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 # http://www.experts-exchange.com/Databases/MS_Access/Q_21862812.html


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

Eric Sherman earned 2000 total points
ID: 16756623
Try ..



Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Implementing simple internal controls in the Microsoft Access application.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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