Excel data to an Access table

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I have an excel spreadsheet set up like a form. This form has 6 pieces of
data the user puts in. Once they are filled in the user needs to click on a
command button and have those 6 pieces of data dumped (appended) into an
Access database. After that is done I want to clear the 6 fields where the
data was in Excel.

Can someone help me?

Thanks,

Les
 
Les,

If you know VBA you can try writing a reinitialization statement in the
module and call it after the append to ACCESS, else, write a macro to clear
the fields and call the macro after the command button to append the data to
ACCESS. The best approach depends on your comfort with VBA and/or macros.

Rick
 
Hi Rick,

Unfortunately I am not as familiar with VBA as I am in VB-6. Can you show
me how to set up the command button to send data from XL to Access (ie send
XLS.fileABC.column to ACCESS.fieldname etc....)? What command button
event should I use and how to null out those 6 fields. If it is involved
then I understand.

Lets say for example I have 3 fileds called field1, field2 and field3 that
has data in them and that upon clicking the button I want to send it to
(appended of course) to Access with fields access1, access2 and access3.
Does this example make life easier?

Thanks,

Les
 
Les,

Sending the data to Access is a bit more complicated, more for the fact that
usually the data transfer is the other directions (at least in my
experience). Let me think about the easiest method to send you an example
that you can apply with little complication. I will try to get back to you
by Tuesday if possible....Monday's are horrible for me.

Re: the issue of clearing the cells....thats very simple. Create your macro
( or hidden command that you reference through code or button (which we we
chat about next week) using the code below:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("B4").ClearContents
Range("B6").ClearContents
Range("C4").ClearContents
Range("C6").ClearContents
Range("D4").ClearContents
Range("D6").ClearContents
ActiveSheet.Protect
End Sub

I did this the easiest method possible for you to see how its done
basically.

Go to Tools, Macros, Visual Basic Editor and double-click on the sheet name
containing the cells. Paste the above code in the sheet. Change the cells
to reference your specifc cells, then save the sheet. Close it and go back
to your form(worksheet).

Now, create the command button and link the code you pasted to its name.

When you save and go back to your sheet it will clear the cells upon
clicking it.

Hope this helps and I will try to get back with you when possible. And, if
I can come up with an easy way to send an example.

Maybe someone else has a good clear example already?

Best of luck....

Later,

RS
 
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Hi Rick,

First let me thank you for your help here!! I will try to play with some
ideas and hope to hear from you if you find the time.

Once again thanks my friend,

Les
 
Les,

I did some looking over the weekend and did not have any examples handy to
send to you but, my suggestion that follows should work.

Try having your button call a VBA module that sets up an ADO connection to
your ACCESS dbase and runs an append qry. Then have it clear the fields as
indicated previously.

For example:

Step 1: Create your connection from Excel to Access using ADO
Dim oConn As New ADODB.Connection

Set oConn.ConnectionString = "DSN=AccessDB;...."

Step 2: Write an append query using your fields and the ADO connection
Step 3: Clear the cell contents as before.




I think that if you look on google or elsewhere you can locate the complete
syntax for the statement.

Despite not knowing VBA very well you should be able to use your VB skills
and the resources you locate to build the statement. I dont have an example
or I would send it to you.

Once again, perhaps someone else has one handy.

Hope this at least serves to guide you in a diretion that works until other
offer their opinions as well.

Best of luck!

RS
 
Back
Top