+ Reply to Thread
Results 1 to 2 of 2

Check for Primary Key

  1. #1
    BillyRogers
    Guest

    Check for Primary Key

    Here a QA program that I have in Excel. the user clicks a button then this
    program loops through all the mdb files in a folder on our network. Each
    database has the same 12 tables but the databases all have different names.
    The program does a query on each table---just a simple record cound and then
    prints the database name, table name and record cound in the spreadsheet. it
    works really well and has reduced a 6 hour job to about 20 minutes. there's
    just one more thing I need to add. I need to check each table for a primary
    key. If there is one I would like to add it to this line as a variable.

    ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
    rs.Fields("Count").Value

    thanks,


    Sub CommandButton1_Click()
    Dim rs As Recordset
    Dim SQlcmd As String
    Dim myTables As Variant
    Dim table As Variant
    Dim DBName As String
    Dim rows As Integer

    Call testfile

    On Error Resume Next

    Set LastCellInA = Sheet3.Range("A:A").SpecialCells(xlCellTypeLastCell)
    UsedRowsInA = LastCellInA.Row



    myTables = Array("[FDMS Billing Fees]", _
    "[FDMS Card Entitlements]", _
    "[FDMS Card Specific Amex]", _
    "[FDMS FEE History]", _
    "[FDMS financial history]", _
    "[FDMS financial history 2]", _
    "[FDMS Link New Xref]", _
    "[FDMS Merchant ABA/DDA New]", _
    "[FDMS Merchant Funding Category DDAs]", _
    "[FDMS Merchant Control Data]", _
    "[tblFDMSInternationalGeneral]", _
    "[tbl_FDMS_PhaseII_Additional_info]")



    'DBName = ListBankNames.Value


    For rows = 1 To UsedRowsInA

    DBName = Sheet3.Cells(rows, 1)


    For Each table In myTables

    SQlcmd = "Select Count(*) as [Count] From " & table

    Set rs = New ADODB.Recordset

    rs.Open Source:=SQlcmd, _
    ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data
    Warehouse\Dallas\MASSCD\AccessDatabases\" + _
    DBName + "; User Id=admin; Password="

    Range("A65000").End(xlUp).Offset(1, 0).Activate




    ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
    rs.Fields("Count").Value

    Next table

    Next rows

    Application.DisplayAlerts = False

    Call TextToColumns

    Application.DisplayAlerts = True
    End Sub


    Public Sub testfile()
    Dim fso, fo, fl, f
    Dim r
    r = 1
    Set fso = CreateObject("Scripting.filesystemobject")
    Set fo = fso.getfolder("N:\Data Warehouse\Dallas\MASSCD\AccessDatabases\")
    Set fl = fo.Files
    For Each f In fl
    If Right(f.Name, 3) = "mdb" Then
    Sheet3.Cells(r, 1) = f.Name
    r = r + 1
    End If
    Next
    End Sub
    --
    Billy Rogers

    Dallas,TX

    Currently Using Office 2000

  2. #2
    Jake Marx
    Guest

    Re: Check for Primary Key

    Hi BillyRogers,

    You can use the OpenSchema method of the Connection object to return a
    resultset of the indexes a table has. It would be easier to manage if you
    had a Connection object set up - then you could open the recordset and the
    schema from that same connection.

    Dim cn As ADODB.Connection
    Dim rs2 As ADODB.Recordset

    Set cn = New ADODB.Connection

    With cn
    .ConnectionString = "<your connection string here>"
    .Open
    End With

    Set rs2 = cn.OpenSchema(adSchemaIndexes, _
    Array(Empty, Empty, Empty, Empty, table)) '/ assumes table var
    contains table name
    rs.Find ("PRIMARY_KEY = True")

    If Not (rs.EOF Or rs.BOF) Then
    MsgBox "table contains primary key"
    Else
    MsgBox "no primary key"
    End If

    rs2.Close
    Set rs2 = Nothing

    To open your other Recordset, you can use the Execute method of the
    Connection object. And you don't need to set rs to a new Recordset - that
    will be done via the return value of the Execute method:

    Set rs = cn.Execute("SELECT COUNT(*) AS [Count] FROM [" & table &
    "]")

    This way, you can reuse the connection. Hopefully this makes some sense and
    works for you.

    --
    Regards,

    Jake Marx
    www.longhead.com


    [please keep replies in the newsgroup - email address unmonitored]

    BillyRogers wrote:
    > Here a QA program that I have in Excel. the user clicks a button
    > then this program loops through all the mdb files in a folder on our
    > network. Each database has the same 12 tables but the databases all
    > have different names. The program does a query on each table---just a
    > simple record cound and then prints the database name, table name and
    > record cound in the spreadsheet. it works really well and has
    > reduced a 6 hour job to about 20 minutes. there's just one more
    > thing I need to add. I need to check each table for a primary key.
    > If there is one I would like to add it to this line as a variable.
    >
    > ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
    > rs.Fields("Count").Value
    >
    > thanks,
    >
    >
    > Sub CommandButton1_Click()
    > Dim rs As Recordset
    > Dim SQlcmd As String
    > Dim myTables As Variant
    > Dim table As Variant
    > Dim DBName As String
    > Dim rows As Integer
    >
    > Call testfile
    >
    > On Error Resume Next
    >
    > Set LastCellInA = Sheet3.Range("A:A").SpecialCells(xlCellTypeLastCell)
    > UsedRowsInA = LastCellInA.Row
    >
    >
    >
    > myTables = Array("[FDMS Billing Fees]", _
    > "[FDMS Card Entitlements]", _
    > "[FDMS Card Specific Amex]", _
    > "[FDMS FEE History]", _
    > "[FDMS financial history]", _
    > "[FDMS financial history 2]", _
    > "[FDMS Link New Xref]", _
    > "[FDMS Merchant ABA/DDA New]", _
    > "[FDMS Merchant Funding Category DDAs]", _
    > "[FDMS Merchant Control Data]", _
    > "[tblFDMSInternationalGeneral]", _
    > "[tbl_FDMS_PhaseII_Additional_info]")
    >
    >
    >
    > 'DBName = ListBankNames.Value
    >
    >
    > For rows = 1 To UsedRowsInA
    >
    > DBName = Sheet3.Cells(rows, 1)
    >
    >
    > For Each table In myTables
    >
    > SQlcmd = "Select Count(*) as [Count] From " & table
    >
    > Set rs = New ADODB.Recordset
    >
    > rs.Open Source:=SQlcmd, _
    > ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data
    > Source=N:\Data Warehouse\Dallas\MASSCD\AccessDatabases\" + _
    > DBName + "; User Id=admin; Password="
    >
    > Range("A65000").End(xlUp).Offset(1, 0).Activate
    >
    >
    >
    >
    > ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
    > rs.Fields("Count").Value
    >
    > Next table
    >
    > Next rows
    >
    > Application.DisplayAlerts = False
    >
    > Call TextToColumns
    >
    > Application.DisplayAlerts = True
    > End Sub
    >
    >
    > Public Sub testfile()
    > Dim fso, fo, fl, f
    > Dim r
    > r = 1
    > Set fso = CreateObject("Scripting.filesystemobject")
    > Set fo = fso.getfolder("N:\Data
    > Warehouse\Dallas\MASSCD\AccessDatabases\") Set fl = fo.Files
    > For Each f In fl
    > If Right(f.Name, 3) = "mdb" Then
    > Sheet3.Cells(r, 1) = f.Name
    > r = r + 1
    > End If
    > Next
    > End Sub




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1