Solved

Using ODBC

Posted on 1998-09-17
5
218 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access Weekly query 11 42
Access Database 5 40
Limit number of characters returned to 999 9 31
Runtime Error -2147467259 (80004005) 7 41
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

713 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