Copy Lastrow in Excel to Access database

[xsmurf]

Hi,

I am fairly new to the world of Access and I have a question.

I have a userform that adds/update/delete info in an Access database, that works perfect, it also updates the info in my excel sheet.
It only handles several columns/Fields.

Would it be possible to just copy the Lastrow in my excel sheet to the Access database and add that to the bottom of the table?
Reason I am asking is because for several columns I can specify the Fields in Access, but if I have around 52 columns and need to name everyone of them in my Access database that would take a long time.
It would be easier to just copy the Lastrow into the Access database, and not looking at the Field-names in Access.

Any help would be appreciated.

 

[ranman256]

if you link the excel sheet as an external table, then you can run an append query to copy the last record to your table.

 

[xsmurf]

if you link the excel sheet as an external table, then you can run an append query to copy the last record to your table.

Thank you for your reply, appreciate it

But that means ALL updates in my excel sheet will be done in the Access database.
I only want to make changes to the database through excel macros

 

[welshgasman]

So find the last row (plenty of examples on the net on how to do that) and use the same mechanism you have in your userform to add a record?

 

[ranman256]

something like this:
add Microsoft ADO to your excel REFERENCES, then adjust the table and field names. (and delimiters for strings)

   'add excel record into access db
Public Sub AddDbRec()
Dim con As ADODB.Connection
Dim DB
Dim vProvid
Dim fld
dim sSql as string

Set con = New ADODB.Connection
 
 DB = "C:\folder\genericDB.mdb"
 vProvid = "Microsoft.Jet.OLEDB.4.0"        ' or for Sqlsvr:  "SQLOLEDB"
 
With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & DB & ";Jet OLEDB"
End With
 
sSql = "Insert into tTargTbl (field1, field2) values (" & range("A2") & ",'" &  range("B2") & "')"
con.Execute(sSql)

con.Close
End Sub

 

[Micron]

I sometimes get away with copying a range and using Paste Append (on Ribbon) in Access. If you try that, make sure the column count is the same as the table field count.

 

[xsmurf]

Thank you all for your reply's, appreciate it. And sorry for the late reply.

I decided to just copy the whole excel database to the access database.
The excel records are checked every day (starting from the last time it was checked) so I thought it would be easier to just copy the excel info into the Access Database.
First I delete the Access database and then I copy the excel data into Access.
It works pretty well, but I doesn't always put the data in the right order.
Every time I press my excel userform button it copies the data but the order keeps changing, so press a couple times till the records are in perfect order.

I do not understand why this is happening, anybody has an idea why this is.
here is my code

Private Sub WriteToAccessButton_Click()

    rs.Open "SELECT * FROM Test1", conn, 1, 3
    conn.Execute "DELETE FROM Test1"
    rs.Close

    ' Write data from Excel sheet to Access database
    Dim ws As Worksheet
    Set ws = Worksheets("Test")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    rs.Open "SELECT * FROM Line_1", conn, 1, 3
    
    Dim dataRange As Range
    Set dataRange = ws.Range("A2:CE" & lastRow + 1)

    Dim i As Long
    For i = 2 To dataRange.Rows.Count
        rs.AddNew
        rs!Record = ws.Cells(i, 1).Value
        rs!Dates = ws.Cells(i, 2).Value
        ' rest of my code/columns
        rs.Update
    Next i
    rs.Close
    
    ' Update the ListBox
    PopulateListBox_Access
    UpdateRecordLabels

 

[welshgasman]

Just use TransferSpreadSheet()

There is no inherent order to a table. You decide the order, though the records should get inserted in the order you supply them.

 

[xsmurf]

will research that one, thank you

 

[Micron]

I would be quite surprised if the orientation of the Excel data is suitable for Access tables, i.e. it's probably not normalized. If this is true, you will likely always run into difficulty in getting data in or out of it (aside from transferring from Excel to Access). Anything else will likely raise problems.