How to Link Excel Spreadsheet to MS Access

[vbqueen]

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!

[HiTechCoach]

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.

[vbqueen]

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!

[HiTechCoach]

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?

[vbqueen]

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

[HiTechCoach]

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

myDB = "c:\foldername\mydbfile.mdb"

Change to be your table name:

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

.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.

[vbqueen]

Hello!

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

but i still keep getting the same error..

[HiTechCoach]

do you have full (read and write) permissions of the folder and mdb file?

[vbqueen]

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") = ""