Solved

Using ODBC

Posted on 1998-09-17
5
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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