Solved

Using ODBC

Posted on 1998-09-17
5
214 Views
Last Modified: 2012-05-04
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
Comment
Question by:lsm091798
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Helicopter
ID: 1962655
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
 
LVL 12

Accepted Solution

by:
Trygve earned 200 total points
ID: 1962656
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
 

Author Comment

by:lsm091798
ID: 1962657
It's work with some correction
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1962658
Please post the corrections for reference use.

Have a nice day !
0
 

Author Comment

by:lsm091798
ID: 1962659
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now