does ms sql server support indexing?

[JeffArcherJr]

Can anyone tell me does the Microsoft OLE DB Provider for SQL Server support indexing?

If so, could anyone point me to a vb example of how to do it. I keep getting run time error 3251 "Current provider does not support the necessary interface for Index functionality".

Thanks

 

[Pat Hartman]

What are you trying to do with an index when you get this message?

 

[JeffArcherJr]

Here's my code

Option Compare Database
Option Explicit

Sub TestIndex()
Dim Connection As New ADODB.Connection
Dim Catalog As New ADOX.Catalog
Dim Zip4DistanceDb As New ADODB.Recordset

'open up files
Connection.Open "Provider=SQLOLEDB.1;INITIAL CATALOG=ProspectorSQL;" & _
"Data Source=ROOLAPTOP;INTEGRATED SECURITY=SSPI"
Set Catalog.ActiveConnection = Connection

'zip4distancedb has "zip" as its primary key in form "12345-6789"
Set Zip4DistanceDb = New ADODB.Recordset
Zip4DistanceDb.CursorLocation = adUseServer
Zip4DistanceDb.Open "Zip4DistanceDb", Catalog.ActiveConnection, _
adOpenKeyset, adLockReadOnly, adCmdTableDirect

'test to see if file open
Zip4DistanceDb.MoveFirst
MsgBox "First Zip code is: " & Zip4DistanceDb("Zip")

'test to see if indexing works
If Zip4DistanceDb.Supports(adIndex) Then
MsgBox "indexing supported"
End If
If Zip4DistanceDb.Supports(adSeek) Then
MsgBox "seek supported"
End If

'try to seek on index
Zip4DistanceDb.Seek ("30004-1234")

'close it all down
Zip4DistanceDb.Close
Set Zip4DistanceDb = Nothing
Set Catalog = Nothing
Connection.Close
Set Connection = Nothing

End Sub




The first test to see if file is open works fine. The tests to see if indexing and seeks are supported fail, and then when I try and do a seek I get the error message.

I find it difficult to believe that the server does not support indexing? Have I made some obvious syntax error, or do I have to do something to the server to switch on indexing?

Thanks
Jeff

 

[Pat Hartman]

Sorry, I can't shed any light on this issue. I would never use the seek technique. I always use queries with selection criteria. They are far more efficient and work regardless of whether the table is linked or local.

 

[JeffArcherJr]

Is this something you could give a brief example of?

I have to admit I mostly know what I am doing with VB but know next to nothing about SQL. Is a Query equivalent of my seek something you could demonstrate in a line or two of code?

Thanks
Jeff

 

[Pat Hartman]

Instead of opening a recordset based on a table, open a recordset based on a query. The query needs parameters so the correct data will be selected. You can then use .MoveNext to read each record returned by the query.

Here's a DAO example of how to create a DAO recordset with parameters. Sorry, I don't have an ADO example:

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=176367#post176367

 

[JeffArcherJr]

Quick and dirty solution ...

I couldn't quite get my head round the example you gave me, but it did inspire this quick and dirty solution which works great for my purposes! Thanks!



Sub TestIndex()
Dim Connection As New ADODB.Connection
Dim catalog As New ADOX.catalog
Dim Zip4DistanceDb As New ADODB.Recordset
Dim strSQL As String

'open up files
Connection.Open "Provider=SQLOLEDB.1;INITIAL CATALOG=ProspectorSQL;" & _
"Data Source=ROOLAPTOP;INTEGRATED SECURITY=SSPI"
Set catalog.ActiveConnection = Connection

'zip4distancedb has "zip" as its primary key in form "12345-6789"
Set Zip4DistanceDb = New ADODB.Recordset

'open a sub-set of zip4 database containing only the zip we are looking for
'(following code needed if looping)
'If Zip4DistanceDb.State > 0 Then
' Zip4DistanceDb.Close
'End If

strSQL = "SELECT * FROM Zip4DistanceDb WHERE Zip = '30002-1003'"
Zip4DistanceDb.Open strSQL, catalog.ActiveConnection, adOpenStatic, adLockReadOnly, adCmdText

MsgBox "First Zip code is: " & Zip4DistanceDb("Zip") & " and size is: " & Zip4DistanceDb("Size")

'close it all down
Zip4DistanceDb.Close
Set Zip4DistanceDb = Nothing
Set catalog = Nothing
Connection.Close
Set Connection = Nothing

End Sub

 

[Pat Hartman]

I'm not sure what this code is if it only looks up '30002-1003'. If you are trying to get the first +4 for a 5-digit zip, you'll need to change the criteria and you'll need to add an Order by clause to the query. Recordsets are unordered sets so First means physically first in the recordset not logically first.

 

[JeffArcherJr]

Its just an example

'30002-1003' is just an example to test if the concept works!

Now that I know how to do it, the real code is looking up longitude and latitude for any zip+4 code in the state of Georgia and of course gets passed whatever zip+4 I happen to be working with. Since there is only one longitude/latitude for the centroid any given zip+4 (at least in theory!), it does not matter that I pull the first record since it should be the only record.

In fact the source data (USPS) does have a few errors in it such that some zip+4's have two entries, but it happens very rarely and is not really a big problem. Since I can now quickly search for any given zip+4 I can now write some routines to find the double entries and use some other methods to work out which is likely to be the more accurate one. I don't know why the USPS source data, which I think they got in turn from the US census data, should have inaccuracies, but there you go.

I think this is probably not an especially efficient way to do this since I am opening and closing the database for each search, but it is many many times quicker than searching record by record through an unindexed database and works great for what I want to do.