Solved

Grouped check boxes

Posted on 2004-04-16
2
327 Views
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.

Thanks.
0
Comment
Question by:mdbbound
[X]
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
2 Comments
 
LVL 5

Accepted Solution

by:
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()

'ALTER TABLE MyTable ADD COLUMN NewColName ....

'UPDATE MyTable Set NewColName = OldColname

'ALTER TABLE MyTable DROP COLUMN 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

Rgds

Mark
0
 
LVL 14

Assisted Solution

by:bluelizard
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
etc.....


--bluelizard
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 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