Search results

Thanks, Galax...I'm still kinda green with SQL Server so if parameter queries are a facet of the SQL Server platform I may need to learn more about them. But I agree about getting small recordsets. Problem in this particular setting is, the department I fairly recently joined has hundreds of...

Bob, this looks like a gold mine! Thank you!! I think I see what I needed in the basSetIndex module. This is great! I'm overdue to go home at the moment, but I am definitely going to give this a try tomorrow. I'll post again afterwards. Thanks again...

Thanks, I'm going to back-burner this for now. I'm not sure it makes any practical difference. This particular SQL View is small--just 400 records, so an index probably wouldn't make any noticable difference in speed (oh, I wish I had time to test that!). But later on we will be migrating...

Oops...maybe I should have provided a little more info... The link in Access is connecting to a SQL Server view rather than a table. SQL Server views, according to what I read at msdn can be indexed if they are made directly from tables, but not if they get data from other views as this one...

I needed to create a linked table with VBA. To do this I used the following code. Set aTDef = myRPG.CreateTableDef(<NewLinkedTableName>) aTDef.Connect = <Appropriate SQL Server connect string> aTDef.SourceTableName = <New SQL Server View Name> myRPG.TableDefs.Append aTDef This works fine. But...

I've needed a refresher so often on this, I made myself a one-page cheat sheet. Maybe it will be useful to you. Good luck...

Okay, if you don't need the original sources to be updated with the additional data you enter in the large file, then the problem becomes very easy. You already have a union query, right? Then the next step is to open a new query, add your union query as the data source for the new query...

Ah...much easier! And very cool...thanks!

Try it without the ? No: Is null or (Like "*?@my.lonestar.edu*") Yes: Is null or (Like "*@my.lonestar.edu*")

I've often wondered why some queries won't let you enter data and others will. But for a union query, the reason is a little easier for me to understand: each column has data from several different sources...if the union query were to let you enter data directly, where would the data go? Which...

Problem solved. See basic code under "Can Link be changed w/o opening db" From here it's just a matter of applying this technique within a loop.

Eureka! Lots of head scratching later...yes it can be done. Here is the code. Public Const NewLinkName As String = "LegacyName" Public Const OldLinkName As String = "LegacyName" Public Const SrcTablName As String = "LegacyName" Public Const strShortCon As String =...

Consider four databases: Controller.accdb, RPG.accdb, OldSource.accdb, and NewSource.accdb. RPG.accdb has a link to Table_MyData in OldSource.accdb. This link needs to be redirected to Table_MyData in NewSource.accdb. I need to write VBA code in Controller.accdb to accomplish this without...

Without seeing it, it is hard to say for sure, but the first thing I would check is primary keys and indexes. RenterID should be a primary key. The field linked to RenterID on the "many" side of the relationship must NOT be a primary key (however, it can be a part of a composit primary key--a...

You are correct: any table that links to the Renter table must contain the RenterID as a field. And yes, this is redundant in a way, but that kind of redundancy is okay. What you would want to avoid, for example, is storing the renter's name twice (or more). For example, if you had multiple...

My dept has an aging Access repository with dozens of tables. We have hundreds of Access files (report generators) that have links to the repository tables (over 4000 links in all). We are replacing the Access repository with a SQL Server repository. The Access Report Generators, however, will...