Saving Excel Userform to Access Database

  • Thread starter Thread starter Katie
  • Start date Start date
K

Katie

I have created a userform in Excel and the spreadsheet that it populates is
getting to large to manage. I would like to populate an Access database
from this userform instead.

All text boxes, combo boxes, etc. are named the same as the columns in the
database. Is there a way to save/append the Excel userform to the Access
database?

Thanks in advance for any help.
 
Katie said:
I have created a userform in Excel and the spreadsheet that it populates is
getting to large to manage. I would like to populate an Access database
from this userform instead.
Is there a way to save/append the Excel userform to the Access
database?
Click to expand...

You can use ADO in VBA code to connect to your Jet database and
execute an INSERT INTO sql statement. There are lots of ADO Excel to
Jet examples, google it e.g.

http://groups.google.com/groups?c2c...+OR+onedaywhen+group:microsoft.public.Excel.*
All text boxes, combo boxes, etc. are named the same as the columns in the
database.
Click to expand...

What a coincidence! <g> Unless there is a good reason for making your
sql dynamic (e.g. schema is dynamic = poor design?), limit the textbox
naming to being a happy coincidence and hard code as e.g.

strSql = "INSERT INTO MyJetTable (MyTextCol, MyIntCol) VALUES ("
strSql = strSql = & "'" & txtMyTextCol.Text & "'"
strSql = strSql = & "," & txtMyIntCol.Text & ");"

Better still, create a PROCEDURE on the database server (Jet DB) that
takes the new values as parameters e.g.

CREATE PROCEDURE
KatiesStoredProc (
new_MyTextCol TEXT,
new_MyIntCol INTEGER
)
AS
INSERT INTO MyJetTable (MyTextCol, MyIntCol)
VALUES (new_MyTextCol, new_MyIntCol)
;

Then your sql text from the client (Excel) would look like this:

strSql = "EXEC KatiesStoredProc "
strSql = strSql = & "'" & txtMyTextCol.Text & "'"
strSql = strSql = & "," & txtMyIntCol.Text

Jamie.

--
 
Back
Top