Results 1 to 9 of 9

How to Link Excel Spreadsheet to MS Access

This is a discussion on How to Link Excel Spreadsheet to MS Access within the Microsoft Access forums, part of the Question Forums category; Hello! I need help on linking an excel spreadsheet (with multiple sheets and tables) to MS Access 2007. The idea ...

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    20

    Default How to Link Excel Spreadsheet to MS Access

    Hello! I need help on linking an excel spreadsheet (with multiple sheets and tables) to MS Access 2007. The idea is that when a user opens up the excel sheet, a login userform will appear. This login userform will connect the excel spreadsheet to the ms access database. This way, whenever the user inputs new data into the fields in the excel spreadsheet (using a userform), the tables and sheets in ms access also get populated. Excel will serve as the user interface while access will collect all data being inputted into excel (note: when data sheets in excel are cleared, data sheets should NOT be cleared in the access database). I will just put the ms access file in my c: drive for example, and my excel file on the desktop.

    Thank you in advance!
    Last edited by vbqueen; Sep 14th, 2010 at 12:23 AM.

  2. #2
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: How to Link Excel Spreadsheet to MS Access

    It is possible to do what you want will a lot of VBA coding in Excel.

    Why not just use a form in Access? It will be a whol;e lot less work to create and Access will do most of the work for you.
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




  3. #3
    New Member
    Join Date
    Sep 2010
    Posts
    20

    Default Re: How to Link Excel Spreadsheet to MS Access

    Hi! Can you suggest the VBA codes to perform the action? I have already created forms in Excel. By the way, I am a VB newbie. Thanks!

  4. #4
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: How to Link Excel Spreadsheet to MS Access

    Quote Originally Posted by vbqueen View Post
    Hi! Can you suggest the VBA codes to perform the action? I have already created forms in Excel. By the way, I am a VB newbie. Thanks!
    I have never tried it or see in done.

    IMHO, using Excel for a data entry form to write data directly to an Access database is using the wrong tool for the job.

    If you are a VBA newbie then I would definitely recommend using an Access form. You can do it with little or possible no VBA code. You can recreate teh form in Access a lot faster than you can learn to write the code to handle an unbound form in Excel to write to an Access database.

    Curious, why must you use Excel as the Front end to an Access database?


    If you must use Excel, then here are the basic steps to programming an unbound for to write data.


    1) create a connection to the database
    2) open a recordset
    3) create a new record (.AddNew)
    4) populate each field in the new record with data from controls on the form
    5) save (.update) the record
    6) close the recordset
    7) close the connection

    Will you be using ADO or DAO?
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




  5. #5
    New Member
    Join Date
    Sep 2010
    Posts
    20

    Default Re: How to Link Excel Spreadsheet to MS Access

    Hello again!

    Thank you, the idea is

    I would like to automate exporting excel files to my access database (multiple users will be using excel so as not to mess with my access database).

    I found the orig code on the net, tried manipulating it for my database but i keep getting run time error 3024 could not find file 'mydbfile.mdb'

    My file is there, and microsoft dao 3.6 object library has already been checked under my references.

    Kindly help? please...I am using excel and access 2007 (although i saved my database file as .mdb and my excel as .xlsm since i need userforms for my excel, because the multiple users cannot also manipulate the excel sheets manually because all data will be entered from the userforms only)


    Private Sub CommandButton1()
    Dim db As Database
    Dim rs As Recordset
    Dim r As Long
    Dim myDB As String

    myDB = "mydbfile.mdb"


    Set db = OpenDatabase(myDB)

    Set rs = db.OpenRecordset("exceltbl", dbOpenTable)

    ' get all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew
    .Fields("ID") = Range("A" & r).Value
    .Fields("FirstName") = Range("B" & r).Value
    .Fields("LastName") = Range("C" & r).Value
    .Fields("Section") = Range("D" & r).Value

    .Update
    End With
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    MsgBox "Appended " & r - 1 & " Records to your database", vbOKOnly, "Confirmation"
    Range("B2:I205") = ""

    End Sub

  6. #6
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: How to Link Excel Spreadsheet to MS Access

    I assume that this code will be in the Click event of a form button

    You will need to modify the code to match your names.


    Change to be the full path the the Access database

    Code:
    myDB = "c:\foldername\mydbfile.mdb"
    Change to be your table name:

    Code:
    Set rs = db.OpenRecordset("[Your Table Name Here]", dbOpenTable)
        
    You will need to change the lines that assign values to fields to use a reference to your Excel form's controls

    Code:
    .Fields("YouFieldNameHere") = ControlName.Value
                
    You will need to create a line like about for every control on the form to add it's value to a field in the record.
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




  7. #7
    New Member
    Join Date
    Sep 2010
    Posts
    20

    Default Re: How to Link Excel Spreadsheet to MS Access

    Hello!

    I already put the source of the file as D:\new\mydbfile.mdb

    but i still keep getting the same error..

  8. #8
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: How to Link Excel Spreadsheet to MS Access

    do you have full (read and write) permissions of the folder and mdb file?
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




  9. #9
    New Member
    Join Date
    Sep 2010
    Posts
    20

    Default Re: How to Link Excel Spreadsheet to MS Access

    Hello Again! I tried to do it on another computer, renamed my variables, etc. The error went away. However, what the code does is it "cuts" the data from the excel sheet and "pastes" it unto the access database. What I want it to do is just "copy" the data from the excel sheet and "paste" it unto the access database. Also, if in case the excel sheets get cleared, the data pasted into the database will not be erased. Can you help me with this, please? My code is written below. Thank you!!

    Dim db As Database
    Dim rs As Recordset
    Dim r As Long
    Dim myDB As String



    myDB = "D:\Documents and SettingsMy Documents\myDB.mdb"


    Set db = OpenDatabase(myDB)

    ' open the database
    Set rs = db.OpenRecordset("Rectbl", dbOpenTable)

    'get all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("ID") = Range("A" & r).Value
    .Fields("FullName") = Range("B" & r).Value
    .Fields("LastName") = Range("C" & r).Value
    .Fields("Location") = Range("D" & r).Value
    .Fields("Region") = Range("E" & r).Value
    .Update ' stores the new record
    End With
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    MsgBox "Appended " & r - 1 & " Records to your database", vbOKOnly, "Confirmation"
    Range("A2:E200") = ""

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com