ACCESS TO SQL

[Shah]

Hi all,

Is there a simple method to read an access 97 mdb file an insert all the record into SQL database from VB?

Thank a lot guys

[coolbiz]

Use ADO to read from Access97 and then dump it into SQL.

Sub TransferData(oDBSrc as ADODB.Connection, oDBNew as ADODB.Connection, szTableName as string)
' note, this proc assumes the conenction to both DBs have been made and passed in via the first
' 2 parameters. It also assumes that TABLE pointed by szTableName exist on both DB and has the
' same structure
dim oRSSrc as new ADODB.Recordset
dim oRSNew as new ADODB.Recordset

' open table on both db
call oRSSrc.Open(szTableName, oDBSrc)
call oRSNew.Open(szTableName, oDBNew)

' loop through the old table
while not oRSSrc.EOF
With oRSNew
.AddNew
.Fields(0).Value = oRSSrc.Fields(0).Value
.Fields(1).Value = oRSSrc.Fields(1).Value
.Fields(2).Value = oRSSrc.Fields(2).Value
.Update
End With
wend

' close recordsets
if (oRSSrc.State <> adStateClosed) then oRSSrc.Close
if (oRSNew.State <> adStateClosed) then oRSNew.Close
End Sub




-Cool Bizs