Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Using ODBC

When i attach table from Oracle it ask to define unique index using Access dialog. Me need to define unique index myself by programm without Dialog. May be some parametrs of connect string. How cam i do it???
0
lsm091798
Asked:
lsm091798
  • 2
  • 2
1 Solution
 
HelicopterCommented:
Unless you have hundreds of these to do it's far easier to create a data definition query and manually replace the table/field parameters as you go along. Have a look under create index in help.
0
 
TrygveCommented:
We use the following function to rebuild all our attachments including setting the indexes. See the table description at the end of the posting for details on the tables involved in the function.

Function RefreshAttachments(pDeleteFirst As Boolean, Optional TableName As String)

    Dim db As Database
    Dim RS, rsPDL As Recordset
    Dim tdf As TableDef
    Dim qry As QueryDef
    Dim i As Integer
    Dim bConfirm As Boolean
   
    DoCmd.Hourglass True
   
    Set db = DBEngine(0)(0)
    Set rsPDL = db.OpenRecordset("SELECT * FROM SD_ProjectDataLocations WHERE PID='KUP'")
   
    If Not Len(TableName) = 0 Then
        Set RS = db.OpenRecordset("SELECT * FROM SD_TableDefs WHERE Name Like '" & TableName & "*' ORDER BY Name")
    Else
        Set RS = db.OpenRecordset("SELECT * FROM SD_TableDefs ORDER BY Name")
    End If

           ' Attach tables listed in SD_TableDefs
           
            While Not RS.EOF
                On Error Resume Next
                    If pDeleteFirst Then db.TableDefs.Delete RS!Name
                On Error GoTo Err_RefreshAttachments
                If DCount("[Name]", "MSysObjects", "[Name]='" & RS!Name & "'") = 0 Then
                    Set tdf = db.CreateTableDef(RS!Name)
                    tdf.Connect = rsPDL!ConnectString
                    tdf.SourceTableName = "dbo.v" & RS!Name
                    db.TableDefs.Append tdf
                End If
                If Len(RS!uniqueindex) > 0 Then
                    On Error Resume Next
                        CurrentDb().Execute "DROP INDEX __uniqueindex ON " & RS!Name
                    On Error GoTo Err_RefreshAttachments
                    DoCmd.RunSQL "CREATE UNIQUE INDEX __uniqueindex ON " & RS!Name & " (" & RS!uniqueindex & ")"
                End If
NextTableSQL:
            RS.MoveNext
            Wend
             
       
    RS.Close
    Exit Function
       
Err_RefreshAttachments:

    If Err = 3011 Then
        MsgBox "Could not find table: " & RS!Name
        Resume NextTableSQL
    Else
        MsgBox "Error while reattaching tables!  " & Err.Description
    End If

End Function


Table SD_TableDefs;
FieldName - Type - Example - Description

Name - Text - DC_Codes - Table Name
UniqueIndex - Text - [CodeType],[Code] - The Index you want for the table
.

Table SD_ProjectDataLocations;
PID - Text - KUP - We use this to locate different projects to different data sources
Name - Text - Test Project - Name of the project
RDBMS - Text - SQL Server - Description of the data storage type
ConnectString - Text - ODBC;DRIVER={SQL Server};SERVER=OurSQL;APP=Pims;WSID=PimsClient;DATABASE=Pims - Connection string for the tables

Hope this helps !
Trygve
0
 
lsm091798Author Commented:
It's work with some correction
0
 
TrygveCommented:
Please post the corrections for reference use.

Have a nice day !
0
 
lsm091798Author Commented:
Function RefreshAttachments(base As Integer)

Dim db As DATABASE
Dim RS, rsPDL As Recordset
Dim tdf As TableDef
Dim qry As QueryDef
Dim i As Integer
Dim bConfirm As Boolean

DoCmd.Hourglass True

Set db = DBEngine(0)(0)
Set RS = db.OpenRecordset("SELECT * FROM ODBC WHERE BASE_ID=" & base & " and id>41")

' Attach tables listed in SD_TableDefs
While Not RS.EOF
    Debug.Print RS!MYNAME
    On Error Resume Next
    db.TableDefs.Delete RS!MYNAME
    On Error GoTo Err_RefreshAttachments
    If DCount("[Name]", "MSysObjects", "[Name]='" & RS!MYNAME & "'") =

if object will be have the same name it not will work first

0 Then
        Set tdf = db.CreateTableDef(RS!MYNAME)
        tdf.Connect = "ODBC;ConnectString=quicb;DSN=dbn_bank;UID=DIKO;"

        tdf.SourceTableName = RS.oraclename
        db.TableDefs.Append tdf
    End If
    If Len(RS!uniqueindex) > 0 Then

???  BUBUH if index will create automatic (primary key) it not work


        On Error Resume Next
        CurrentDb().Execute "DROP INDEX __uniqueindex ON" & RS!MYNAME
        On Error GoTo Err_RefreshAttachments
        DoCmd.RunSQL "CREATE UNIQUE INDEX __uniqueindex ON " & RS!MYNAME & " (" & RS!uniqueindex & ")"
    End If
NextTableSQL:
    RS.MoveNext
Wend
RS.Close
DoCmd.Hourglass False
Exit Function
Err_RefreshAttachments:
If err = 3011 Then
    MsgBox "Could not find table: " & RS!MYNAME
    Resume NextTableSQL
Else
If err = 3283 Then Resume Next
    MsgBox "Error while reattaching tables! " & err.Description
    DoCmd.Hourglass False
'    Resume
End If
End Function


BUT NOW IT WORK

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now