Copying data from one database to another

[RobP]

I am opening an Excel spreadsheet as a database using VB code and now I am trying to save the data in an existing Access database. I am having trouble tranferring the data. Anybody have any code suggestions?

[Ravi Kiran]

Hi,

I had never worked on Excel sheets before.
Thanks, your problem gave me a chance to!.
It looks as if the naming of the fields in
Excel sheet is a little difficult. I think/hope they
would have explained that in docs. Anyway, here is
what i found:
1. You have to access the "SheetN" as a recordset.
2. If the sheetN already consists of something,
then the Field names start with the contents of the first Non-empty cell,
3. If the sheet is totally empty, then the (default) Field names would be "F1", "F2" etc.

Are you transfering from Access to Excel or viceversa?.
Here is a piece of code that transfers
data from a access mdb to Excel sheet. (VB 5.0's code.)
Scene : A form with 2 data controls and a btn.
Data1 opens the Access database, and data2 Excel sheet. i.e data2.connect = "Excel 8", .Databasename is some Xls . On btn click, the contents of the 3 fields of the db would be transfered to the approp sheet.


private Sub Command1_Click()
Dim lpRecset1 as Recordset, lprecset2 as Recordset
set lpRecset1 = Data1.Recordset
set lprecset2 = Data2.Database.OpenRecordset("Sheet3$") ' CHANGE here to approp. sheet
'set lprecset2 = Data2.Recordset ' you can use this also
lpRecset1.MoveFirst
If lprecset2.RecordCount <> 0 then
lprecset2.MoveFirst
lprecset2.Edit
else
lprecset2.AddNew
End If

Do
' change in these lines to approp fileds of the database.
lprecset2("F1") = lpRecset1("ID")
lprecset2("f2") = lpRecset1("Eng string")
lprecset2("F3") = lpRecset1("Jap string")
lprecset2.Update
lpRecset1.MoveNext
If lpRecset1.EOF = true then Exit Do
If lprecset2.EOF then
lprecset2.AddNew
else
lprecset2.MoveNext
lprecset2.Edit
End If
Loop 'While Not lpRecset1.EOF
set lpRecset1 = nothing
set lprecset2 = nothing
End Sub





Open the Xls and check. Play around with the names a little.

It was an interesting problem!. I hope this solves it.

Ravi Kiran

[RobP]

I appreciate your response Ravi!

I am actually starting with an Excel spreadsheet which is a timesheet. Then I am pulling the employee name from a certain cell, the project numbers from a certain range of cells and the corresponding hours from another range of cells and copying them into a table in an Access database. Unfortunately the existing timesheet is not totally setup in a table form (i.e. the employee name is at the top and underneath the project numbers and hours are in a table) Do you think the code you wrote may still work? I will continue to work on it, but if you have any further ideas, I would appreciate it.

Thanks, Rob