Importing Excel data from from to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form in Excel. I would like to be able to allow users to
fill out the form, have that data link to an Access table and then have a
clear form for the next user who opens the document (adding a new row to the
Access table). I downloaded a template wizard from Microsoft
that allows you to create an Excel template and link the template to Access,
but once you have filled out the template, only that specific data links. I
tried creating worksheets using the template, but that does not work as it
does not link to the Access table. Any suggestions?
 
Hi Jennifer,

I don't know the template wizard you mention. It sounds as if you just
want a one-way system in which Excel users can fill out the form and
append the result as a new record in a database table, with no
rquirement for them to view or edit previously-entered data.

If that's right, what I'd probably do is use code like this to build and
execute a SQL statement that appends the record. What follows is
untested "air code". It assumes that each value you want to upload is in
a cell on a worksheet which you have defined as a named range. (If you
want to get the values from controls on a UserForm instead, the
principle is the same but the syntax is slightly different.) I've
assumed the field names are F1, F2, F3:


Sub AppendARecord()
Dim oJet As DAO.DBEngine
Dim oDB As DAO.Database
Dim strSQL As String

Const DB_NAME = "C:\Temp\Test 2003.mdb" 'name of database
Const TBL_NAME = "My_Table" 'name of table

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "INSERT INTO " & TBL_NAME _
& " (F1, F2, F3) VALUES ("
With ActiveWorkbook
'add value of numeric field to strSQL
strSQL = strSQL & .Names("F1").RefersToRange.Value _
& ", "
'add value of text field, using apostrophes to delimit it
strSQL = strSQL & "'" _
& .Names("F2").RefersToRange.Value & "', "
'another numeric field
strSQL = strSQL & .Names("F3").RefersToRange.Value
'finish off the SQL statement
strSQL = strSQL & ");"
End With

'append the record
oDB.Execute strSQL, 128 'dbFailOnError
'tidy up
oDB.Close

End Sub
 
Will this work even if they are not in range if I just use the names of the
cells (they aren't all in one column or row, but spread out)?
 
Also, how do you put the SQL into Excel? I have just used the function
features before now.
 
Hi Jennifer,

You can use syntax like this
Activeworkbook.Worksheets("CDF").Range("B6").Value
to get the contents of an individual cell.

The advantage of naming the cells instead of referring to them by
address is that if you insert or delete rows or columns, the name stays
with the cell. If you use an address like "B6" and then insert a row
above, the code will be broken until you remember to change B6 to B7.

From what you've said it sounds as if your form is an Excel worksheet
rather than a UserForm. Open the VBA editor (Alt-F11), go to
Tools|Options, and on the Editor tab make sure "Require variable
declaration" is checked.

Then insert a new module and paste the code into it.

Next, modify the code so it's using your names and locations for fields,
tables, the Access database, etc. To test the code, make sure the Debug
toolbar is displayed (in the VBA editor) and use the Step Into button to
step through it line by line. Once it's working, you can run it either
by selecting it from the list in Excel's Tools|Macro|Macros... command
or by putting a button on the worksheet or a toolbar (see Excel help for
that).
 
I am getting an error when I try to run the debug. The first line Dim oJet
As DAO.DBEngine is highlighted and it says "Compile Error User-defined
type not defined". Sorry if these are obvious questions, I'm a newbie at
this. Also, can I choose all of the cells that require user input as a range
called Data and make that work?
 
1) In the VBA editor, go to Tools|References and set a reference to the
Microsoft DAO 3.6 Object Library.

2) Yes. You can use this syntax to refer to individual cells in the
named range:

ActiveWorkbook.Names("Data").RefersToRange.Cells(1).Value

So if the first cell in "Data" corresponds to a numeric field ID in the
table, and the second cell corresponds to the text field LastName, the
code could look like this:

strSQL = "INSERT INTO " & TBL_NAME _
& " (ID, Data, F3) VALUES ("
With ActiveWorkbook
'add value of numeric field to strSQL
strSQL = strSQL & .Names("Data").RefersToRange.Cells(1).Value _
& ", "
'add value of text field, using apostrophes to delimit it
strSQL = strSQL & "'" _
& .Names("LastName").RefersToRange.Cells(2).Value & "', "
'another numeric field
strSQL = strSQL & .Names("F3").RefersToRange.Cells(3).Value
'finish off the SQL statement
strSQL = strSQL & ");"
 
Back
Top