Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Proj and SQL Server (Access 2000)

    I am accessing a SQL Server database from an Access project. I have a View that selects a single column from a stored procedure.

    SELECT name AS TName
    FROM dbo.[DEF-Discrete Table Names - no exams]

    This returns the names of all the tables I want from the database.
    I then have a stored procedure that returns Primary Key information.

    ALTER PROCEDURE dbo.[DEF-Primary Keys]
    AS
    declare tnames_cursor CURSOR
    for
    select [tname] from dbo.[DEF-PKeys Input]
    open tnames_cursor
    declare @tablename sysname
    fetch next from tnames_cursor into @tablename
    while @@fetch_status = 0
    begin
    EXEC sp_pkeys @tablename
    fetch next from tnames_cursor into @tablename
    end
    close tnames_cursor
    deallocate tnames_cursor

    However, this will only return the first row.

    If I look at it in the SqlServer Object Browser/de###### and walk through it, it will return all rows.

    Am I missing something simple?

    Hope someone can help- I am climbing up the wall!!

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Sorry mate but I'm just learning this myself so this could be the wrong tree:::

    Fetches the row n rows from the last row fetched. If n is positive, the row n rows after the last row fetched is fetched. If n is negative, the row n rows before the last row fetched is fetched. >>>>>If n is 0, the same row is fetched again.<<<<<<

    Like I said, I'm right on the bottom of the curve!!!

  3. #3
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Rupert,

    Thanks for your mail. The value that I am checking against 0 is @@FETCH_STATUS. This tells you whether or not the last FETCH returned any record and allows you to decide when the loop is finished.

    Still Puzzled!

    Graeme.

  4. #4
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    OK. New question. What I am actually trying to do (with the original question I posted) is print a report showing the primary key columns on all tables. Do you know a better way (ort any way!) of doing this? In an Accessmdb, I use the tables collection and get the indexes that are PKs but this does not work in projects.

    Ta.

    Graeme.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Proj and SQL Server (Access 2000)

    You are actually trying to get into the bowels of SQL Server and do things that the ADP interface may or may not support. In addition, I believe that there are system stored procedures in SQL Server that will do what you are trying to do.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Wendell,

    Thanks for your comments. Yes, I guess I am trying to get into the bowels. I am trying to list the Primary Key columns for all my database tables. The Stored Procedure I am using (sp_pkeys) is a SQL Server one and takes a mandatory argument of a table name - it returns just what I need. This is fine if I pass it a single table name. However, in the procedure I listed in my original question, I put it in a loop to get each table name in turn. I only get 1 row back (the first table). Still stumped!

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Graeme,

    there is a useful but little-publicized library called DMO that can help you with this. It is fully documented in the MDSN help on Microsoft's support site, and will be installed on your PC if you have MSDE or SQL client tools (but the help file is not always present). You will need to set a reference to Microsoft SQL DMO Object library to use it.

    You can then do things like this:
    set oServer = new SQLDMO.SQLServer
    oServer.LoginSecure = True
    oServer.connect "server-name"
    set oDatabase = oServer.Databases("dbname")

    for each oTable in oDatabase.Tables
    ...
    if (oTable.SystemObject = False) then
    ...
    for Each OKey in oTable.Keys
    ...
    if (oKey.Type = SQLDMOKey_Primary) Then
    ...

    etc etc

    where oTable etc are DMO variables representing tables and so on.

    If you do a general internet search for DMO or SQLDMO you can find quite a few programming examples.

    Jeremy

  8. #8
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Thanks Jeremy. I will try this and see if I have success. gee they make it hard!!!

    Graeme.

  9. #9
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Proj and SQL Server (Access 2000)

    Thanks Jeremy. Worked a treat! I still am no wiser though as to why the SQLServer stored procedure did not work as expected. Here is the code I ended up with:

    Public Function NewTableList()
    'This function populates the DEF-PKey Column Data table with the names of primary key columns
    Dim oServer As SQLDMO.SQLServer
    Dim oTable As SQLDMO.Table
    Dim oDatabase As SQLDMO.Database
    Dim oKey As SQLDMO.Key
    Dim oCol As SQLDMO.Column
    Dim counter
    Set oServer = New SQLDMO.SQLServer
    oServer.LoginSecure = True
    oServer.Connect ("MyServer")
    Set oDatabase = oServer.Databases("MyDatabase")
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set con = New ADODB.Connection
    con.ConnectionString = "provider=sqloledb.1;data source=" & GetDBName() & ";initial catalog=" & GetConStr() & " datasql;integrated security=SSPI"
    con.Mode = adModeReadWrite
    con.Open
    Set cmd.ActiveConnection = CurrentProject.Connection
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    rec.Open "[DEF-PKey Column Data]", con, adOpenStatic, adLockOptimistic
    DoCmd.SetWarnings False
    'Clean out the table
    DoCmd.RunSQL "delete from [DEF-PKey Column Data]"
    For Each oTable In oDatabase.Tables
    If (oTable.SystemObject = False) Then
    For Each oKey In oTable.Keys
    If (oKey.Type = SQLDMOKey_Primary) Then
    For counter = 1 To oKey.KeyColumns.Count
    rec.AddNew
    rec![PKey Table Name] = oTable.Name
    rec![PKey Name] = oKey.Name
    rec![PKey Column Name] = oKey.KeyColumns(counter)
    rec.Update
    Next
    End If
    Next
    End If
    Next
    End Function

Posting Permissions

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