excel 2013 user form to update access 2003 database

Mike_CS

New Member
Joined
Jun 21, 2017
Messages
24
Hi All

I was wondering if anybody knows a quick and dirty way for an excel (2013) user form to populate an access (2003) database.

I have created the database in access and they match the questions in the user form but i'm having difficulty in getting the to to communicate (a connection has been made).

I need to use excel as the front end as access licenses are limited so anybody can complete this form.

There's various parts to the user form. For example the text boxes can be freely typed in, the combo boxes are populated by vlookups giving people choices in a dropdown and the labels are a mixture of environ details (first name, last name, user id) as well as chosen dates from a calendar and a 'Today' label for the date last modified column in the database.

I have looked online and saw things mentioning ADO and DAO but most (if not all) examples only use cell values and when i try and translate it to label/textbox values etc. i get errors so have removed everything and am starting from scratch.

If i have missed a post with the exact same query i'm sorry.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Typically, sending data is as simple as:
Code:
<!--StartFragment-->[FONT=&quot]With rs
[/FONT]
[FONT=&quot]    .Fields("LOANNUMBER") =LOANNUMBER[/FONT]
[FONT=&quot]    .Fields("LOANDATE") = LOANDATE[/FONT]
[FONT=&quot]    .Fields("CUSTNAME") = CUSTNAME[/FONT]
[FONT=&quot]'repeat for all required fields[/FONT]
[FONT=&quot]    .Update[/FONT]
[FONT=&quot]    .Close[/FONT]
[FONT=&quot]End With[/FONT]
<!--EndFragment-->
In this example, "LOANNUMBER" is the field name and I am using a TextBox with the same name.

If your error is occurring within this area, I would think it's an issue of formatting between excel and access. If the Access field is set up for a date value, but you send NULL (""), you'll get an error.

There's a ton of information on it here: http://www.globaliconnect.com/excel...-data-objects-library-ado&catid=79&Itemid=475
 

Watch MrExcel Video

Forum statistics

Threads
1,099,098
Messages
5,466,637
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top