Solved

Using ODBC

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 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