Linking MS SQL tables into MS Access 2003 VBA - too many indexes

[asorkoram]

Hello,
I have a 3rd party app that uses SQL server to store its data. I am also creating a MS Access 2003 app to access the data. When I import the tables a few of the tables have over the 32 index limit. Is there a way to link the tables without indexes or count the amount of indexes and stop after set amount during the import? I cannot change the indexes in SQL because they are used by 3rd party app so I need a way to access the tables and link them; getting around this problem of 32 index limit. THANK YOU.

[Jim Doherty]

Hello,
I have a 3rd party app that uses SQL server to store its data. I am also creating a MS Access 2003 app to access the data. When I import the tables a few of the tables have over the 32 index limit. Is there a way to link the tables without indexes or count the amount of indexes and stop after set amount during the import? I cannot change the indexes in SQL because they are used by 3rd party app so I need a way to access the tables and link them; getting around this problem of 32 index limit. THANK YOU.

Hi

How are you accessing the data? what is your frontend file format? unless you specifically Neeeeeeeeed to import tables to a localised copy of a database (mdb format) my advice is to use an MS ADP project to access SQL server that way all the table remain on the server

Regards

Jim

[asorkoram]

Hi Jim,

I have to link the table in MS Access not a access data page. I have to do all types of reports and also upgrades are done to the SQL data tables and I have to be able to read all that to change forms in my Access app. I have been able to Link the tables but it copies all the Index info aswell...My code:

Public Function MakeLinkODBC(ServerName As String, _
DNSName As String, _
DatabaseName As String, _
UserName As String, _
UserPass As String) As Boolean

On Error GoTo errODBC
Dim SQLDb As DAO.Database
Dim dbLocal As DAO.Workspace
Dim I As Long
Dim tblName As String
Dim tdfCurrent As DAO.TableDef
Dim nbrTable As Long
Dim idx As DAO.Index

Dim ind As DAO.Index 'Each index of this table.
Dim fld As DAO.Field 'Each field of the index
Dim iCount As Integer
Dim strReturn As String 'Return string
Dim tdf As DAO.TableDef 'Name of table
Dim tdfS As String
Dim ind1 As Index


Set dbLocal = DBEngine.Workspaces(0)
Set SQLDb = dbLocal.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DATABASE=" & DatabaseName & _
";UID=" & UserName & _
";PWD=" & UserPass & _
";DSN=" & DNSName & _
";SERVER=" & ServerName & "")
nbrTable = SQLDb.TableDefs.Count - 1
Dim Z
Dim x
Dim j

x = 0
For I = 0 To nbrTable
tblName = SQLDb.TableDefs(I).Name



Call UpdateProgressMeter("M1 Conversion and Linking Prog :" & vbCrLf & DatabaseName & " now doing...", I, Val(nbrTable), "M1 Progress Linking form", I & "/" & nbrTable)

'Add the
If SQLDb.Name = "Master" Then

If InStr(tblName, ".") > 0 Then
tblName = Right(tblName, Len(tblName) - InStr(tblName, "."))
End If

Set tdfCurrent = CurrentDb.CreateTableDef(tblName, dbAttachSavePWD)
tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" & UserName & ";PWD=" & UserPass & ";DSN=" & DNSName


If tblName = "sysprocesses" Then
tdfCurrent.SourceTableName = tblName
CurrentDb.TableDefs.Append tdfCurrent
End If

Else

'// Exclude the systems objects
If Left(tblName, 1) <> "~" And InStr(tblName, ".sys") = 0 Then
If InStr(tblName, ".") > 0 Then
tblName = Right(tblName, Len(tblName) - InStr(tblName, "."))
End If
If tblName = "PartMemos" Then

'MsgBox ("1")
Set tdfCurrent = CurrentDb.CreateTableDef(tblName, dbAttachSavePWD)
'MsgBox ("2")

tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" & UserName & ";PWD=" & UserPass & ";DSN=" & DNSName
'MsgBox ("3")

'Proccess the tables and views and do name change
If InStr(tblName, "dbo") = 0 Then
'Do just tables
'
'MsgBox ("4")
tdfCurrent.SourceTableName = tblName

MsgBox ("Start Section")

CurrentDb.TableDefs.Append tdfCurrent
MsgBox ("Did It!!")

'Rename the DD tables to "System_DD
CurrentDb.TableDefs(tblName).Name = Replace(CurrentDb.TableDefs(tblName).Name, "DD", "M1DD_")

Else
'Do all the views for the tables
tdfCurrent.SourceTableName = tblName
CurrentDb.TableDefs.Append tdfCurrent
End If

End If

End If

End If
Next I

SQLDb.Close
dbLocal.Close

MakeLinkODBC = True
Forms![frmSetup-ODBC]![Annuler].Caption = "Close"

Exit Function

errODBC:
If Err.Number = 3010 Then 'Object already exists
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 3059 Then
MakeLinkODBC = False
Exit Function
Else
MsgBox Err.Description, vbExclamation, "SQL Server"
MakeLinkODBC = False
Exit Function
End If

End Function

My app gets the error where it says MsgBox ("Start Section")
The error is "There are too many index in Table "PartMemos"

Isn't there a way to separate out the fields and data from the Indexes??

Thanks, Ayal

[Jim Doherty]

Hi Jim,

I have to link the table in MS Access not a access data page. I have to do all types of reports and also upgrades are done to the SQL data tables and I have to be able to read all that to change forms in my Access app. I have been able to Link the tables but it copies all the Index info aswell...My code:

Public Function MakeLinkODBC(ServerName As String, _
DNSName As String, _
DatabaseName As String, _
UserName As String, _
UserPass As String) As Boolean

On Error GoTo errODBC
Dim SQLDb As DAO.Database
Dim dbLocal As DAO.Workspace
Dim I As Long
Dim tblName As String
Dim tdfCurrent As DAO.TableDef
Dim nbrTable As Long
Dim idx As DAO.Index

Dim ind As DAO.Index 'Each index of this table.
Dim fld As DAO.Field 'Each field of the index
Dim iCount As Integer
Dim strReturn As String 'Return string
Dim tdf As DAO.TableDef 'Name of table
Dim tdfS As String
Dim ind1 As Index


Set dbLocal = DBEngine.Workspaces(0)
Set SQLDb = dbLocal.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DATABASE=" & DatabaseName & _
";UID=" & UserName & _
";PWD=" & UserPass & _
";DSN=" & DNSName & _
";SERVER=" & ServerName & "")
nbrTable = SQLDb.TableDefs.Count - 1
Dim Z
Dim x
Dim j

x = 0
For I = 0 To nbrTable
tblName = SQLDb.TableDefs(I).Name



Call UpdateProgressMeter("M1 Conversion and Linking Prog :" & vbCrLf & DatabaseName & " now doing...", I, Val(nbrTable), "M1 Progress Linking form", I & "/" & nbrTable)

'Add the
If SQLDb.Name = "Master" Then

If InStr(tblName, ".") > 0 Then
tblName = Right(tblName, Len(tblName) - InStr(tblName, "."))
End If

Set tdfCurrent = CurrentDb.CreateTableDef(tblName, dbAttachSavePWD)
tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" & UserName & ";PWD=" & UserPass & ";DSN=" & DNSName


If tblName = "sysprocesses" Then
tdfCurrent.SourceTableName = tblName
CurrentDb.TableDefs.Append tdfCurrent
End If

Else

'// Exclude the systems objects
If Left(tblName, 1) <> "~" And InStr(tblName, ".sys") = 0 Then
If InStr(tblName, ".") > 0 Then
tblName = Right(tblName, Len(tblName) - InStr(tblName, "."))
End If
If tblName = "PartMemos" Then

'MsgBox ("1")
Set tdfCurrent = CurrentDb.CreateTableDef(tblName, dbAttachSavePWD)
'MsgBox ("2")

tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" & UserName & ";PWD=" & UserPass & ";DSN=" & DNSName
'MsgBox ("3")

'Proccess the tables and views and do name change
If InStr(tblName, "dbo") = 0 Then
'Do just tables
'
'MsgBox ("4")
tdfCurrent.SourceTableName = tblName

MsgBox ("Start Section")

CurrentDb.TableDefs.Append tdfCurrent
MsgBox ("Did It!!")

'Rename the DD tables to "System_DD
CurrentDb.TableDefs(tblName).Name = Replace(CurrentDb.TableDefs(tblName).Name, "DD", "M1DD_")

Else
'Do all the views for the tables
tdfCurrent.SourceTableName = tblName
CurrentDb.TableDefs.Append tdfCurrent
End If

End If

End If

End If
Next I

SQLDb.Close
dbLocal.Close

MakeLinkODBC = True
Forms![frmSetup-ODBC]![Annuler].Caption = "Close"

Exit Function

errODBC:
If Err.Number = 3010 Then 'Object already exists
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 3059 Then
MakeLinkODBC = False
Exit Function
Else
MsgBox Err.Description, vbExclamation, "SQL Server"
MakeLinkODBC = False
Exit Function
End If

End Function

My app gets the error where it says MsgBox ("Start Section")
The error is "There are too many index in Table "PartMemos"

Isn't there a way to separate out the fields and data from the Indexes??

Thanks, Ayal

Ayal,

Using an .ADP file frontend format does not mean you have to use data access pages!! I don't use them myself not much point! if you are going to web enable use ASP. With an ADP frontend you can have forms, views,reports macros menus modules in fact most everything that an MDB has.
Using an ADP means you have an built in connection to the server using datalink UDL connectivity. The big plus side here is that the tables remain on the server. You can SEE them in the tables windows as you would if you were using an MDB? and do whatever you need to. There is no setting up of ODBC connections locally or otherwise to contend with and you wouldnt have 'the too many indexes' issue you currently have trying to import table info and data into lesser data storage beast as is Access compared against the SQL Server environment.

Unless you have something else in mind there is no reports that I know of that cannot come off of an ADP file compared to an MDB with the exception of dead easy crosstab functionality and plus everything is server side so client connection overhead is to a minimum.

The simple answer to your problem currently is to 'reduce' the indexes on the server table causing the problem to keep within Access 'limitations' (32 in all) and try again?

Jim