Linked Table Index question

[jDa]

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 got curious: I manually created a link to the same table using the External Data functionality on the ribbon. Then I enumerated the properties of the VBA-created link and compared them to those of the manually created link.

Mostly, they were the same. But the manually created link contains a unique index and the VBA-created link does not.

First, should this matter? Second, if so, how can I add an index from VBA? When I've tried to create/append an index, the operation is not allowed for linked tables.

 

[Galaxiom]

You need to send the CREATE INDEX command to be processed on the server where the table is located.

Use ADODB Connection and Command Objects.

 

[jDa]

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 does. So, as I understand, there is no possibility of creating an index on the server side.

This leads me to believe that the index I am seeing on the manually created table is a pseudo index. I'm thinking it must be created and managed entirely by Access. During manual link creation, in the External Data dialog in Access, right after selecting the SQL Server view to link to, a list of fields from the view appears with the instruction to choose up to ten fields that uniquely identify each record. I'm thinking this has to be where the index is created. Unless I'm missing something (good possibility) I don't think any changes are happening on the server side when this manually created link is established. So, my question is, how can I make this same unique index thing happen during the process of programmatically creating the link? So far I haven't been able to find any info about that on the 'Net.

Nevertheless, I'll try what you've suggested and perhaps come to understand as I go...thanks so much for your help!

 

[Galaxiom]

There are a number of circumstance where Access (or more precisely Jet/ACE) creates an index automatically itself. It does this while working with Find and Filter in recordsets too.

Please do post back if you find out what causes the different behaviour as I am curious too.

 

[jDa]

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 hundreds of tables (some of them quite large) from Access to SQL Server and redirecting the links of their dependent RPGs. I would really not like to cause reports to start crawling along because I redirected links without properly creating indexes...so I'm probably not going to let this question drop. I'll post if I learn more. Please do the same, and thanks once again for your comments. I'm wondering after your last post if maybe I might do something in VBA with Find or Filter and sort of accidentally provoke it into creating an index. Ahh, so much fun to be had...so little time.

Another, more minor, mystery is why the VBA-created link enumerates only 12 properties while the manually created link enumerates 22. I've considered just forgetting about working with TableDefs and going directly to the ribbon...try to just program the same steps in VBA that a user goes through when manually creating a link with the External Data functionality...never tried that, though. Not sure I want to... Anyhow, thanks again.

 

[boblarson]

Not sure if this will help, but we had issues (from what I've been told) with needing to index the SQL Views so someone had created this tool to run against our frontends. The code is dependent upon adding an index name, the view name, and what field/fields make up the PK.

I am leaving the information in the tblViews table just so you can see kind of how it is used. Hopefully this will help.

 

[Galaxiom]

Rather than Views you should use parameter queries so the server just returns the records you specify and works from its own indexes.

I only know about the index created on recordsets because I read about it. The whole process is hidden but something to be aware of as creating that index takes time on a large recordset.

It is one reason it is important to keep recordsets as small as possible and why it is better to open them repeatedly with different criteria rather than retreiving a big one and filtering it.

 

[jDa]

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...

 

[jDa]

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 legacy Access front ends most of which were created by users who, while often quite clever, are not programmers. These front ends often have dozens of nested queries many of which expect to see a linked table called "Wheel Of Fortune." (Oh, please don't even ask how that name came about--it has nothing to do with what the table holds!! <chuckles>) Now, these queries may in some cases be horribly inefficient, but to clean them up? I do NOT want to go there...LOL. So I just want to pacify these legacy front ends (a little paliative care) by giving them just what they expect: a linked table called "Wheel Of Fortune" only now linked to a different sourcetable in a different database (and I promise, the SQL View they now link to is definitely not named "Wheel Of Fortune." Hee haw...).