Search results

Regarding adding the fuelcard record when the tech record is added. In SQL Server we do this all the time using triggers which fire a batch of SQL commands. You can do the same sort of thing in the AfterInsert event of your Tech record.

If you have a form which allows you to select from a dropdown list of both new and existing tech, it makes no difference. You ares still inserting a new row in the Tech-Card link table whether it's their first card, or a replacement.

Yes, as long as there aren't too many techs or cards. I try to limit data in drop downs to about 20 rows. Much more than that and it is unfair to the user, and accuracy goes way down. When I have a lot of records for the user to choose from, I build a dedicated form tat they can use to filter...

All true. If you add a DateAdded column to the Tech table, if it is the case that the card is ordered on the same day a new tech is added, the value of True in the FuelCardRequired bit column and the DateAdded column allow you to report on fuel card backorder.

OK. I would do this is a class, because it keeps code loosely coupled away from forms and affords code reuse, but that's just how I architect this stuff. You're going to need to run SQL INSERT statements against the database to insert a row in the FuelCard table, and in the link table. Since...

Correct, we don't need a fuelcardnumber column. It's one-to-many relationship between tech and card, because you need historical data on fuel card assignments, but only one card is current. So there is a row in the junction table for every fuel card ever assigned to the tech, and one row in...

His old card will remain assigned to him, but it will not be the current card. Only one card will be the current card. This design supports many fuel cards being assigned to the tech, and also the differentiation between the two classes of cards - current and assigned in the past but not now...

So are you going to SQL Server? Doesn't much matter when you built the UI, it will go to SQL OK. The advantages of having the back end in SQL Server are enormous and worth any learning curve.

Oh, yeah I'd forgotton about SSMA. It didn't work for me, but it looks great. I prefer to pull data from the SQL Server side rather than push it from Access, because I have loads of experience as a SQL Server guy, and much less in Access, and the SQL Server tool set is richer for ETL. Hope...

Another lesser advantage is that if a value changes in one of the lookup tables (Region again) it is automatically promulgated to all the Texh records that reference it.

I didn't explain the JOINs completely. What we are going for here in atomicity. Take the Region example. The literal value for the region (East, West, whatever) should only exist in the database once, that being in the Region Table. Any other table that references a Region should store the ID of...

Close. You should link on the ID column in the foreign tables. So, for example, change Region to RegionID, integer, and relate RegionID to Region.ID, and so on for each table.. JOINs in integer columns are much faster than on text fields. If you need to keep historical data on fuel cards, put...

Oh yeah - you probably know this, but for the sake of completeness: When you add a table to SQL Server, it won't show up in Linked Table Manager until you do the initial linking of it to Access, under External Data | ODBC Database. This was mysterious to me and hard to discover when I first...

It's possible they are living under a different schema than dbo. Have you tried scrolling down past all the dbo tables? In one of my databases that's what happened, new tables are listed below dbo. This happened to me because I have to use SQL authentication - the database is far away in a...

All is not lost. Rather than downgrade your Access version, just don't use the upsizing wizard. Instead, import your Access tables using SQL Server Management Studio (Right click the database, then Tasks, then Import Data). You'll get a wizard that will let you import all your Access tables...

OK, so I would store UserID in this table. UserGroup, Region and FieldStore in separate tables.

I think you need to upgrade the database to SQL 2008, or downgrade the Access version.

Not a practical limit. I have 182 linked SQL Server tables and views in an Access 2010 database and it's all working fine. http://blogs.office.com/b/microsoft-access/archive/2006/06/05/access-2007-limits.aspx

Looks pretty good. Some column names suggest that their data should be stored in separate tables: UserID (maybe) UserGroup Region FieldStore ReasonForLeaving (only if you want to establish domain integrity on this, not if it is to be freely entered) And, it's just a matter of preference, and...

OK, I get it now. A product is made up of raw materials. For example, a floor might be made from one of several different raw materials, perhaps pergo, or a wood laminate, but never both. Each raw material can have a different price. I've done this before. Here's my suggestion. You have a...