Consulting

Results 1 to 6 of 6

Thread: Solved: Excel data backup to MS Access

  1. #1

    Post Solved: Excel data backup to MS Access

    Hi dear friends..

    I have prepared a break tracker in excel sheet. I want particular data that to be stored in MS access table when I click "save" button on the Excel sheet.

    I have a data in C5, D5, E5, F5 & G5 cells.

    Please help me.

    Thanks.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Why not just save a copy of the workbook?

    Or save the data in another Excel workbook, appending to it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Quote Originally Posted by xld
    Why not just save a copy of the workbook?

    Or save the data in another Excel workbook, appending to it.
    Dude its a timesheet tracking file, I want that kind of information if employee save the data then they could not edit this file again or they can not make any other changes into that file. and the important thing there are 10 people who have different worksheet I want all 10 people data on MS access when they click on the save button.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    I can't see that changes my suggestion. Either do it all in Excel, or all in Access is my opinion.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Quote Originally Posted by xld
    I can't see that changes my suggestion. Either do it all in Excel, or all in Access is my opinion.
    Ok fine thanks.

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    You can use something like this. Notice that the first row must contain the Field name. I would suggest that you make a Hidden sheet, add the first row column names and then type "=" in the formula bar and click the cell from another sheet for that column. You then have 2 rows.

    [VBA]Sub demo()
    Dim objRS As Object, nwindPath As String
    Set objRS = CreateObject("ADODB.Recordset")
    nwindPath = ThisWorkbook.Path & "\nwind.mdb"

    Dim r As Range
    [a1] = "LastName"
    [b1] = "FirstName"
    [a2] = "Hobson"
    [b2] = "Kenneth"
    Set r = [a1:b2]
    r.Name = "MyRange"

    objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

    Set objRS = Nothing
    End Sub[/VBA]


    Replace:
    [VBA] nwindPath = ThisWorkbook.Path & "\nwind.mdb"[/VBA]
    with your path to your mdb.

    Replace:
    [VBA] Dim r As Range
    [a1] = "LastName"
    [b1] = "FirstName"
    [a2] = "Hobson"
    [b2] = "Kenneth"
    Set r = [a1:b2]
    r.Name = "MyRange"[/VBA]
    with your range. e.g.
    [VBA]WorkSheets("YourHiddenSheetName").Range("A1:B6").Name ="MyRange"[/VBA]
    Or, if you have manullay named MyRange, you can skip this part.

    After doing this in our button's Click Event, save and close the file for them with your special password.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •