p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: RE: Need help


Message #1 by "Mavin Specimen" <mspecimen@h...> on 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


  Return to Index