Solved

Grouped check boxes

Posted on 2004-04-16
2
336 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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