Grouped check boxes

Posted on 2004-04-16
Last Modified: 2010-05-18
I have a form that has a group of checkboxes (around 5).  These check boxes are names of tables in local drive that needs to be updated by copying the most recent version of the same tables residing from the network drive.  At anytime during the day, the user will click all or some of the checkboxes and click a submit button to process the update.  I dont want to use linking table because it is very slow, reeeaaaly slow.

Can someone please help me figure this out.  I'm afraid I will need some VBA and I don't know how to do it.

Question by:mdbbound
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

mleman earned 350 total points
ID: 10840128
Although you dont want to use linking tables, it is proberley the best idea, as you will have to export the tables back after you have finished with them.

another way to do it could be by using the replication process, there fore having a local copy and a network copy of the database, then all you have to do is syncronise them

i have a database the imports data from other databases as follows

Dim pathname    'set path value
Dim projectcode 'set projectcode
Dim route       'put it all together

pathname = Me.Proselection.Column(1)    'extract pathname from selection
projectcode = Me.Proselection.Column(2) 'extract projectcode from selection

'Now put the file path together

route = "k:\" + pathname + "\" + projectcode + "_be.mdb"

' import tabmain

DoCmd.TransferDatabase acImport, "Microsoft Access", route, acTable, "Tabmain", "Tabmain", False

' import contacts table

DoCmd.TransferDatabase acImport, "Microsoft Access", route, acTable, "contacts table", "contacts table", False

' import contacts table

DoCmd.TransferDatabase acImport, "Microsoft Access", route, acTable, "questions", "questions", False

end sub

but then i delete the tables as i dont need to send them back

Private Sub Command10_Click()

DoCmd.DeleteObject acTable, "tabmain"
DoCmd.DeleteObject acTable, "contacts table"
DoCmd.DeleteObject acTable, "questions"

MsgBox "The system has been reset you can now start again", vbInformation, "Cancel Lead Import"

Call resetform

End Sub

i also runn some code inbetween that exports the data to a webserver on my network, this is not all of it but it gives you the idea

Sub sortquestionstable()


'UPDATE MyTable Set NewColName = OldColname


Dim projectcode
projectcode = Me.Proselection.Column(2)

Dim strsql

strsql = "ALTER TABLE Questions ADD COLUMN Leadid CHAR(50)"

DoCmd.RunSQL strsql

strsql = ""

strsql = "UPDATE Questions SET Leadid = [Company ID]"

DoCmd.RunSQL strsql

strsql = ""

strsql = "DROP INDEX [company id] on questions"

DoCmd.RunSQL strsql
strsql = ""

strsql = "ALTER TABLE Questions DROP Column [company id]"

DoCmd.RunSQL strsql

' export questions to correct qtable
strsql = ""

strsql = "INSERT INTO q" + projectcode + " IN '\\somewebserver\d$\database\database.mdb'"
strsql = strsql + "SELECT questions.* FROM questions INNER JOIN tabMain ON questions.Leadid = tabMain.[Company ID] WHERE (((tabMain.Result) Like '*lead*') AND ((tabMain.Quality)=Yes) AND ((tabMain.[leadsheet sent tag]) Is Null))"

DoCmd.RunSQL strsql

DoCmd.DeleteObject acTable, "tabmain"
DoCmd.DeleteObject acTable, "contacts table"
DoCmd.DeleteObject acTable, "questions"

MsgBox "The Data has been uploaded and system has been reset you can now start again", vbInformation, "Cancel Lead Import"

Call resetform

End Sub

the above code uses a combo box to select a database and then imports 3 tables, and then exports to another database,

mightr help you


LVL 14

Assisted Solution

bluelizard earned 150 total points
ID: 10840184
what i usually do to create such "offline" tables (requires almost no VBA...):

1) create a local table by copying the original's table *structure*
2) *link* the original table and name it something like "L_origtable"
3) create a delete query that removes all records from the local table
4) create an append query that inserts all records from the linked table to the local table
5) create a macro that first calls the delete query, then the update query
(repeat for every table)

now, base all your forms, reports etc. on the *local* tables (created in 1).  your "submit" button should then call the macro created in 5, and this will "replicate" the data (you might want to go to the preferences of access and turn off the confirmations for "record changes" and "action queries" to avoid such messages).

that's it.

if you want to distinguish which tables to update, use code such as this in the "submit" button:

if Me.checkbox1.value = true then
  DoCmd.RunMacro "replicatetable1"
end if
if Me.checkbox2.value = true then
  DoCmd.RunMacro "replicatetable2"
end if


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question