I forgot Dim rsADO as ADODB.Recordset but still nothing. The error 

I get is "Item not found in collection"



>From: "Mavin Specimen"

>Reply-To: "Access"

>To: "Access"

>Subject: [access] RE: Need help

>Date: Fri, 29 Jun 2001 13:09:01

>

>I am trying to load an mdb into SQL 7 without hardcoding the 

field names. I am trying to do this by cycling through the fields collection 

in. The sticking point is actually assigning the value from the mdb 

to the ADO. Here is the 

code I have so far and I will seperate the point where I am stuck 

with bullets(*)

>

>Public Sub sub_Load_SQL_Table(vTblName As String, vDBName As String)

>Dim cnn As ADODB.Connection

>Dim vFN As ADODB.field ' field name for ADO

>Set cnn = New ADODB.Connection

>cnn.Open "DSN=final_UpliteData"

>Dim ws As DAO.Workspace, db As DAO.Database, rsDAO As DAO.Recordset, 

tdf As DAO.TableDef

>Dim vfld As DAO.field

> Set ws = Workspaces(0)

> Set db = ws.OpenDatabase(vDBName)

> Set tdf = db.TableDefs(vTblName)

> For Each vfld In tdf.Fields

> Set rsADO = New ADODB.Recordset

> rsADO.CursorType = adOpenKeyset

> rsADO.LockType = adLockOptimistic

> rsADO.Open "msysIcons", cnn, , , adCmdTable

> rsADO.AddNew

> Set rsDAO = db.OpenRecordset(vTblName)

>

>**********************************************************

> rsADO.Fields(vFN).Value = rsDAO.Fields(vfld).Value

>

>**********************************************************

> Next vfld

>rsADO.Update

>Set db = Nothing

>Set ws = Nothing

>rsDAO.Close

>rsADO.Close

>cnn.Close

>End Sub

>

>Just a beginner at this and its probably a glaring mistake I 

am making. Thoughts\comments appreciated. Thanks

>

I've taken liberties with your code to offer my suggested approach. I will

say outright that I am not ADO literate but am somewhat proficient in DAO.



Walt





Public Sub sub_Load_SQL_Table(vTblName As String, vDBName As String)

Dim cnn As ADODB.Connection

Dim vFN As ADODB.field          ' field name for ADO

Set cnn = New ADODB.Connection

cnn.Open "DSN=final_UpliteData"

Dim ws As DAO.Workspace, db As DAO.Database, rsDAO As DAO.Recordset, tdf As

DAO.TableDef

Dim vfld As DAO.field

Dim x as long

Set ws = Workspaces(0)

Set db = ws.OpenDatabase(vDBName)

Set rsDAO = db.OpenRecordset(vTblName)

Set tdf = db.TableDefs(vTblName)



Set rsADO = New ADODB.Recordset

rsADO.CursorType = adOpenKeyset

rsADO.LockType = adLockOptimistic

rsADO.Open "msysIcons", cnn, , , adCmdTable



do while not rsDAO.EOF

  rsADO.AddNew

  For x = 0 to rsDAO.fieldcount -1

     rsADO.Fields(x) = rsDAO.Fields(x)

  Next x

  rsADO.Update    'I'm not sure, but I believe ADO update occurs by default

  rsDAO.movenext

Loop



set rsDAO = Nothing

set RSADO = Nothing

Set db = Nothing

Set ws = Nothing

cnn.Close



End Sub

Walt thanks for your help but it still doesn't work. My objective is not 

to hard code the variable names in case they ever change. There is no such 

thing as fieldcount so I used recordcount instead but still got same 

result.



Thanks







>From: Walt Morgan 

>Reply-To: "Access" 

>To: "Access" 

>Subject: [access] RE: Need help (Correction) 

>Date: Fri, 29 Jun 2001 09:34:44 -0500 

> 

>I've taken liberties with your code to offer my suggested approach. I will 

>say outright that I am not ADO literate but am somewhat proficient in DAO. 

> 

>Walt 

> 

> 

>Public Sub sub_Load_SQL_Table(vTblName As String, vDBName As String) 

>Dim cnn As ADODB.Connection 

>Dim vFN As ADODB.field ' field name for ADO 

>Set cnn = New ADODB.Connection 

>cnn.Open "DSN=final_UpliteData" 

>Dim ws As DAO.Workspace, db As DAO.Database, rsDAO As DAO.Recordset, tdf As 

>DAO.TableDef 

>Dim vfld As DAO.field 

>Dim x as long 

>Set ws = Workspaces(0) 

>Set db = ws.OpenDatabase(vDBName) 

>Set rsDAO = db.OpenRecordset(vTblName) 

>Set tdf = db.TableDefs(vTblName) 

> 

>Set rsADO = New ADODB.Recordset 

>rsADO.CursorType = adOpenKeyset 

>rsADO.LockType = adLockOptimistic 

>rsADO.Open "msysIcons", cnn, , , adCmdTable 

> 

>do while not rsDAO.EOF 

> rsADO.AddNew 

> For x = 0 to rsDAO.fieldcount -1 

> rsADO.Fields(x) = rsDAO.Fields(x) 

> Next x 

> rsADO.Update 'I'm not sure, but I believe ADO update occurs by default 

> rsDAO.movenext 

>Loop 

> 

>set rsDAO = Nothing 

>set RSADO = Nothing 

>Set db = Nothing 

>Set ws = Nothing 

>cnn.Close 

> 

>End Sub 

Mavin,



Sorry for the confusion. I'm attaching a sample of DAO code that I just

confirmed as working:



This routine will open the table STORE in the MDB called SIRSYS97 and print

the contents

of field 0 from the STORE table.



Walt





Private Sub Command1_Click()

   Dim x As Long

   Dim db As Database

   Dim rs As Recordset

   Set db = OpenDatabase("D:\SIRSYS\SIRSYS97.MDB")

   Set rs = db.OpenRecordset("Store", dbOpenTable)

   rs.MoveLast

   rs.MoveFirst



   For x = 0 To rs.Fields.Count - 1

      Debug.Print rs.Fields(0)

      rs.MoveNext

   Next x



   Set rs = Nothing

   Set db = Nothing



End Sub

Mavin,



I may inadvertantly be creating confusion for which I apologize. I DO NOT

use Access, rather VB code when dealing with DAO. I am surmising that there

are significant nuances between the two.



Please excuse the intrusion.



Walt