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
Solved

Grouped check boxes

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

837 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