Home
Sunday, October 22, 2017
10:20:51 AM
Users online: 0   You are here >> Home > Programming

Forums | Programming Forums search
Forum FAQ
   
  1  
Access assistance - help with queries...
N3M3SiS 
2/7/08 8:53:37 AM
Guru

Hey all,

<Background>
Im working in a stockbroking firm at present and have been given the (super-exciting) job of providing a number of reconciliations between the two systems used to record client holdings. This was previously being done in Excel and was an absolute and complete joke; errors, omissions and copy/pasting coming out the yin yang! Ive since ditched this in favor of a more appropriate (I think?) system, Access.
</Background>

Im learning as I go really and am nowhere near as familiar with Access as I would like but im finding i can basically get by.

My question is what is the best way to take queries you have build for one set of data (tables) and apply them to another? Because i need to run these every month (more at the moment because of backlog =( ) and cant see why you would have to make the queries again just because im importing new data?

How do you usually go about this, is it possible to build my query in access with some kind of variable that i can specify which table it should run on each time? Eg. "System1data30062008" VS "System2data30062008".

As i mentioned im really flying by the seat of my pants here so please feel free to point out any idiocy you have encountered with my aforementioned ideas!

Any help appreciated!

Cheers.

-----
nem·e·sis - An opponent that cannot be beaten or overcome...

"He would make a lovely corpse." -Charles Dickens (1812 - 1870)

kikz 
2/7/08 10:34:41 AM
Immortal

Oh well at least Access is a RDMS...
(I take it the limitations of Access have been considered? Is this going to run over a network? Using the MS Jet Engine? and more)

Needs more information. Do the different data sets share a common schema? Maybe you're doing something like have a different table for each month (don't do that please :)).

Sorry, don't know of a way you can change the table the query points to. Is the table an Access table, or is it an linked table? If you're linking to a different data source, eg an Excel spreadsheet, you can keep the same table name in Access, but change the ODBC link. This can be automated in code and a form put on the front, such that you type the name of the excel file and sheet name and the code relinks/refresh the linked table. Alternatively, you could just name the excel file the same each time.

-----
Q6600 | 4Gb PC6400 | 2 x 500Gb RAID 0 | 19" Benq FP591 + 24" Samsung 245B + 19" Dell | 8800GTS 640 Mb + 8400GS 256 Mb | Gigabyte GA-P35-DS3P | Antec P182 | Corsair HX-620 | Thermalright 120 Extreme | Vista x64

eckythump 
16/7/08 11:39:16 PM
Overlord

What kikz said. Without fully understanding what it is you're trying to do, I think I have a vague idea, and indeed, you don't want to have tables with dynamic names if it can be helped.

Using some very basic eckycode to demonstrate:
 
// BAD BAD BAD!
$month = "200806";
do_query("SELECT info,about.rich.fucks from wealthy_pricks-$month");

// Closer to what you want (but likely still horrible from a pro's persepective)
$month = "200806";
do_query("SELECT info,about,rich,fucks from wealthy_pricks WHERE month=$month");


And yes, my magical eckycode isn't meant to be perfect, and I don't think we want to cloud a demonstrated concept with SQL injection protection methods, either. Just getting that in, because there's always one smart arse. :)

-----
"Grandfather had an accident, he got burnt." "Oh no, how bad?" "Well, they don't fuck around at the crematorium."

Jeruselem 
17/7/08 10:05:05 AM
Champion

One thing with Microsoft Access databases - if the database reaches near 2GB, it gets flakey and prone to corruption. You must keep the database compacted and slim to stop it bloating and getting corrupted.

I work with Access databases but the back-end systems are SQL Servers.

I'd suggest you have two databases. One with the back-end Access tables and the other as front-end reporting linking to the back-end. Hence if the reporting database dies, you still have your data in the back-end.


Edited by Jeruselem: 17/7/2008 10:11:14 AM

-----
PC 1: XP Home SP2, Opty 165@1.8Ghz, GEIL 1GB PC3200, 320GB SATA Cuda ES,XFX 9600GSO 580/700x2/1450, Seasonic S12+ 550W
PC 2: XP Home SP2, XP 3000+@2.24 Ghz, 1GB PC3200, 80GB IDE,ASUS nVidia 6800 512MB, Codegen 450W

kikz 
17/7/08 10:29:47 AM
Immortal

Quote by Jeruselem
One thing with Microsoft Access databases - if the database reaches near 2GB, it gets flakey and prone to corruption. You must keep the database compacted and slim to stop it bloating and getting corrupted.

I work with Access databases but the back-end systems are SQL Servers.

I'd suggest you have two databases. One with the back-end Access tables and the other as front-end reporting linking to the back-end. Hence if the reporting database dies, you still have your data in the back-end.


Yep. Good idea to perform the compact nightly after the backup (after the backup because compacts like to fail randomly, corrupting the db). Run it at 3am, when users shouldn't be in. Check for presence of .ldb will tell u that anyway.

Biggest problem with Access is it's use of Microsoft's Jet. Jet copies down records to the client and manipulates them locally. So if a client wants to select an order from the Orders talbe (containing 500,000 records), each of say a kilobyte, Jet copies down all 500,000 then extracts the one it wants. Sounds pretty shite! Now what if you've got 10 users concurrently seaching for orders...

500,000 KB * 10 = 5GB down your network, from the same pc, at once. Fail! All to extract 10KB worth of records.

Pretty obvious Access doesn't scale too well.

In reality, I don't know why people use Access, when there's SQL Server Express Edition/MSDE available. At worst case, do like Jeruselem and run a real DBMS at the back end and use Access as the front end. (adp style).

-----
Q6600 | 4Gb PC6400 | 2 x 500Gb RAID 0 | 19" Benq FP591 + 24" Samsung 245B + 19" Dell | 8800GTS 640 Mb + 8400GS 256 Mb | Gigabyte GA-P35-DS3P | Antec P182 | Corsair HX-620 | Thermalright 120 Extreme | Vista x64

pappes 
28/7/08 9:37:46 PM
Titan

Getting back to the question at hand, it is much easier to rename a table, run the query, then rename the table back. E.G. System1data30062008->System1data->System1data30062008

The other way to do it in access is, rather than run you analytic query directy off the table, run it off another query which just does a select * from table.

Then you can edit the sql of the sub query (view->sql view) and run the analytic query

-----
I am petitioning to be the first person to be awarded a Nobel prize for prescience. It doesn't exist yet but mark my words it will.

  1  
Forums | Programming