Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Grouped check boxes

Posted on 2004-04-16
2
Medium Priority
?
346 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 1050 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 450 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

721 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