Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

ODBC problem

Discussion in 'Information Technology' started by Sharon, Jul 28, 2006.

  1. Sharon

    Expand Collapse

    I have a patent docketing system that's reporting feature is an Access
    database. I had to “build†a licensing module into the software and somehow
    the new reports etc. that I have generated are not being recognized by the
    When I go to Start/Administrative Tools/Data Source (ODBC) and go to the tab
    to configure the system dsn, when I get to the drop down containing the
    server names, there are three; two with exactly the same name (Argon) and one
    that is Argon2.
    I was told by the owner’s of the software that to create a new view, I
    needed to go into Enterprise Manager and drop the current view and also go
    into the Access database and drop that view, then re-create the link to the
    view in the Linking Manager. I have done this, but it is not recognizing the
    “connection.†When I try to run the report, I get a message saying that it
    can’t find the view, but I know the view is there because I can go and look
    at the data generated in Access.
    I went to Doug Steele’s website, and found an article about making a module
    for a DSN-less connection and tried to incorporate that code into my database
    and well, although I don’t think I screwed anything up too bad, it still is
    not working. If this would work, would it fix the problem with the dsn? I
    am sure I didn’t “fill in all the holes†in the code that I needed to, so the
    code is not running. This is the code:

    Option Compare Database

    Type TableDetails
    TableName As String
    SourceTableName As String
    Attributes As Long
    IndexSQL As String
    Description As Variant
    End Type

    Sub FixConnections(Argon As String, IPMaster As String)
    ' This code was originally written by
    ' Doug Steele, MVP djsteele@canada.com
    ' You are free to use it in any application
    ' provided the copyright notice is left unchanged.
    ' Description: This subroutine looks for any TableDef objects in the
    ' database which have a connection string, and changes the
    ' Connect property of those TableDef objects to use a
    ' DSN-less connection.
    ' This specific routine connects to the specified SQL Server
    ' database on a specified server. It assumes trusted connection.
    ' Inputs: Argon: Name of the SQL Server server (string)
    ' IPMaster: Name of the database on that server (string)

    On Error GoTo Err_FixConnections

    Dim dbCurrent As DAO.Database
    Dim prpCurrent As DAO.Property
    Dim tdfCurrent As DAO.TableDef
    Dim intLoop As Integer
    Dim intToChange As Integer
    Dim strDescription As String
    Dim typNewTables() As TableDetails

    intToChange = 0

    Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

    ' Build a list of all of the connected TableDefs and
    ' the tables to which they're connected.

    For Each tdfCurrent In dbCurrent.TableDefs
    If Len(tdfCurrent.Connect) > 0 Then
    ReDim Preserve typNewTables(0 To intToChange)
    typNewTables(intToChange).Attributes = tdfCurrent.Attributes
    typNewTables(intToChange).TableName = tdfCurrent.Name
    typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
    typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
    typNewTables(intToChange).Description = Null
    typNewTables(intToChange).Description =
    intToChange = intToChange + 1
    End If

    ' Loop through all of the linked tables we found

    For intLoop = 0 To (intToChange - 1)

    ' Delete the existing TableDef object

    dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName

    ' Create a new TableDef object, using the DSN-less connection

    Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
    tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
    IPMaster & ";SERVER=" & Argon & _
    tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
    dbCurrent.TableDefs.Append tdfCurrent

    ' Where it existed, add the Description property to the new table.

    If IsNull(typNewTables(intLoop).Description) = False Then
    strDescription = CStr(typNewTables(intLoop).Description)
    Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText,
    tdfCurrent.Properties.Append prpCurrent
    End If

    ' Where it existed, create the __UniqueIndex index on the new table.

    If Len(typNewTables(intLoop).IndexSQL) > 0 Then
    dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
    End If

    Set tdfCurrent = Nothing
    Set dbCurrent = Nothing
    Exit Sub

    ' Specific error trapping added for Error 3291
    ' (Syntax error in CREATE INDEX statement.), since that's what many
    ' people were encountering with the old code.
    ' Also added error trapping for Error 3270 (Property Not Found.)
    ' to handle tables which don't have a description.

    Select Case Err.Number
    Case 3270
    Resume Next
    Case 3291
    MsgBox "Problem creating the Index using" & vbCrLf & _
    typNewTables(intLoop).IndexSQL, _
    vbOKOnly + vbCritical, "Fix Connections"
    Resume End_FixConnections
    Case Else
    MsgBox Err.Description & " (" & Err.Number & ") encountered", _
    vbOKOnly + vbCritical, "Fix Connections"
    Resume End_FixConnections
    End Select

    End Sub

    Function GenerateIndexSQL(TableName As String) As String
    ' This code was originally written by
    ' Doug Steele, MVP djsteele@canada.com
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    ' Description: Linked Tables should have an index __uniqueindex.
    ' This function looks for that index in a given
    ' table and creates an SQL statement which can
    ' recreate that index.
    ' (There appears to be no other way to do this!)
    ' If no such index exists, the function returns an
    ' empty string ("").
    ' Inputs: TableDefObject: Reference to a Table (TableDef object)
    ' Returns: An SQL string (or an empty string)

    On Error GoTo Err_GenerateIndexSQL

    Dim dbCurr As DAO.Database
    Dim idxCurr As DAO.Index
    Dim fldCurr As DAO.Field
    Dim strSQL As String
    Dim tdfCurr As DAO.TableDef

    Set dbCurr = CurrentDb()
    Set tdfCurr = dbCurr.TableDefs(TableName)

    If tdfCurr.Indexes.Count > 0 Then

    ' Ensure that there's actually an index named
    ' "__UnigueIndex" in the table

    On Error Resume Next
    Set idxCurr = tdfCurr.Indexes("__uniqueindex")
    If Err.Number = 0 Then
    On Error GoTo Err_GenerateIndexSQL

    ' Loop through all of the fields in the index,
    ' adding them to the SQL statement

    If idxCurr.Fields.Count > 0 Then
    strSQL = "CREATE INDEX __UniqueIndex ON [" & TableName & "] ("
    For Each fldCurr In idxCurr.Fields
    strSQL = strSQL & "[" & fldCurr.Name & "], "

    ' Remove the trailing comma and space

    strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
    End If
    End If
    End If

    Set fldCurr = Nothing
    Set tdfCurr = Nothing
    Set dbCurr = Nothing
    GenerateIndexSQL = strSQL
    Exit Function

    ' Error number 3265 is "Not found in this collection
    ' (in other words, either the tablename is invalid, or
    ' it doesn't have an index named __uniqueindex)
    If Err.Number <> 3265 Then
    MsgBox Err.Description & " (" & Err.Number & ") encountered", _
    vbOKOnly + vbCritical, "Generate Index SQL"
    End If
    Resume End_GenerateIndexSQL

    End Function

    Any help is appreciated.
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     

Share This Page