PDA

View Full Version : Split Database - Unbound Form - Using SQL Insert versus RecordSet



bvanscoy678
04-08-2015, 07:30 PM
Hello,

I am working on a Access database to collect information on potential infectious disease exposures within our fire department. I am sure there are a lot of different ways of doing it, but I was looking at two. Both involve using an unbound form (back and front end) and VBA code.

I was shown one way of collecting the values in an unbound form and then using DoCmd.RunSQL to insert into my tables.

DoCmd.RunSQL "insert into tbl_radio_inventory (Serial_Number,RID,Company,Display,Date_Issued)values([txt_Serial_Number],[txt_RID],[txt_Company],[txt_Display],[txt_date_issued])"

I am also aware of using a recordset to insert the files into the tables.

I have four tables which are all related via the Incident_ID primary key (incident table main). My idea would be to collect the PK from the form and then set the values to each table using the primary key from the incident table, to insert the FK and remaining data.

The DoCmd looks like it works well, but wasn't sure if the recordset had more benefits. The database will be set up on a network server with a front and back end. The use will be light, but I am trying to learn how to deploy a few other Access databases with heavier traffic.

Thanks for any tips, ideas and/or suggestions. I do understand VBA and can get started with a few good bumps in the right direction. I just have never split a database using a unbound form for user input (they will not be viewing any data, just inputting. ).

Thanks,
Brent

jonh
04-10-2015, 01:30 AM
Why is the form unbound? Where does the PK come from if the form isn't bound?

bvanscoy678
04-10-2015, 05:26 AM
Why is the form unbound? Where does the PK come from if the form isn't bound?

Hi,

The relationships are described in the SQL statement. I could have been a bit more clear, but the main question is a recommendation of updating a record (one or more than one table(s)) using a SQL statement OR using a recordset connection.

Thanks.

jonh
04-10-2015, 07:23 AM
Relationships have nothing to do with whether a form is bound or not.

Anyway.

A query updates one table.
If you want to update 4 tables you need to run 4 scripts.

If the database tables aren't linked supply the db path.

db = "c:\mydb.accd"

tbl = "mytable1"
fld = "fld1,fld2"
val = "'foo','bar'"
currentdb.execute "insert into [" & db & "].[" & tbl & "] (" & fld & ") values (" & val & ")"

tbl = "mytable2"
fld = "fld1,fld2"
val = "'hello','world'"
currentdb.execute "insert into [" & db & "].[" & tbl & "] (" & fld & ") values (" & val & ")"

etc..

bvanscoy678
04-10-2015, 08:54 AM
Yes, that is correct. I will need to run four SQL statements to update 4 tables. I was wondering if there was a better way of doing this; say using an ADO connection and updating the recordset?

In the end, I have a userform that I want to collect data from and input into my database. I have never done that with a split database, so looking at the different options to accomplish the task. My database will be located on a network server with a front and back end.

Thank you.

Brent

jonh
04-10-2015, 01:03 PM
There is no better way of updating a table than with a query/sql.
Code that uses dao/ado to udpdate records is most likely doing it wrong.

bvanscoy678
04-10-2015, 08:28 PM
That sounds good to me! Thanks