Automate Excel upload into static Access Table

[Seanarthur]

I am trying to find a way to automate an 'append' process, whereby a
user can enter data into an excel table and hit a button (macro) to
have the data automatically populated in an existing Access table.

Is this possible? Anyone up for a challenge?

 

[Pranav Wagh[MSFT]]

Hello,
Did you try automating Access from VBA ?

Pranav Wagh

"The Real Stuff Is oUT oF tHE bOX"

http://blogs.msdn.com/pranavwagh

--------------------

From: Seanarthur <[email protected]>
Subject: Automate Excel upload into static Access Table
Date: Fri, 3 Mar 2006 12:41:01 -0600
Message-ID: <[email protected]>
Organization: ExcelTip
User-Agent: ExcelTipForum
X-Newsreader: ExcelTipForum
X-Originating-IP: 63.172.71.3
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: limelight.irubin.com 70.85.25.132
Lines: 1
Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.excel.programming:709076
X-Tomcat-NG: microsoft.public.excel.programming

I am trying to find a way to automate an 'append' process, whereby a
user can enter data into an excel table and hit a button (macro) to
have the data automatically populated in an existing Access table.

Is this possible? Anyone up for a challenge?

 

[Arun]

Actually this is very easy. Make a connection to Access using ADO and
then insert data into the access table.

Do a google on inserting data into access from excel.

Arun.

 

[Seanarthur]

I'm no pro, here.

I've spent quite a bit of time searching today and have found several
examples, all different, that claim to be able to do this. The
simplest ones reference ADO, as was done in the response to my post.
The problem is that I don't know if I have ADO or how to use it.

When I use the ADO commands I'm finding in the examples, I get VB error
messages like, 'user defined variable not defined'.

I've also been trying to figure out a way to do this using ODBC. I
think if I can figure out how to establish a connection to the
database, I can then work my way through inserting the records.
Unfortunately, the examples I'm finding are pretty cryptic to someone
such as myself, who has pretty much jumped head-first into the world of
VB.

I'm not an IT professional. So I doubt I have more than the basic
excel and access packages on my computer. Any advice?

Sean