Writing to specific fields in Access

[ddivers]

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
objExcel.Workbooks.Open "C:\My Documents\book1.xls"


objExcel.Visible = True


For lngCount = 1 To UBound(arrPT1)

objExcel.ActiveSheet.Range("A" & lngCount + 1).Value = arrPT1(lngCount)
objExcel.ActiveSheet.Range("B" & lngCount + 1).Value = arrPT2(lngCount)
objExcel.ActiveSheet.Range("C" & lngCount + 1).Value = arrPT3(lngCount)
objExcel.ActiveSheet.Range("D" & lngCount + 1).Value = arrPT4(lngCount)
objExcel.ActiveSheet.Range("E" & lngCount + 1).Value = arrPT5(lngCount)
objExcel.ActiveSheet.Range("F" & lngCount + 1).Value = arrPT6(lngCount)

Next lngCount

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub


------------------------------------------------------------------------------------
That is the code that my program uses to write an excel file. I need my program to also make the same file, but as a access file. How would i open a access file, and effectively do the same thing but using access instead?

arrPT1 - arrPT6 are just arrays of strings.

[ddivers]

Basically I want to do exactly what the code above does, but I want everything stored in .mdb not .xls. If anyone needs more clarification...just ask please.

[reznor]

I assumed there is an Access table called 'tblTest' which has six fields called 'Field1' to 'Field6'. I would use ADO. Looks someting like this:

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    cn.ConnectionString = "Driver=Microsoft Access Driver (*.mdb);DBQ=c:\test.mdb"
    cn.Open

    rs.ActiveConnection = cn
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Source = "SELECT * FROM tblTest;"
    rs.Open

    For lngCount = 1 To UBound(arrPT1)
        rs.AddNew
        rs.Fields("Field1") = arrPT1(lngCount)
        rs.Fields("Field2") = arrPT2(lngCount)
        rs.Fields("Field3") = arrPT3(lngCount)
        rs.Fields("Field4") = arrPT4(lngCount)
        rs.Fields("Field5") = arrPT5(lngCount)
        rs.Fields("Field6") = arrPT6(lngCount)
        rs.Update
    Next lngCount

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

hope this helps,
...rick

[alex_read]

1st off, start by looking here ...
http://www.vbworld.com/databases/dbtutorial/

[alex_read]

For Microsoft Access databases, stick to DAO rather than ADO as it'll be quicker and more efficient.
Start with the aboove link, then look at these :

http://161.58.186.97/showthread.php?...light=database

http://161.58.186.97/showthread.php?...light=database

[ddivers]

Thanks for all the great information/links!!!