Professional Web Applications Themes

Im CoRnfused - Microsoft Access

MSDE *is* a version of SQL Server. It is the exact same engine. It has a couple of tweaks to keep it from being used on a large scale. (2GB limit and 5 concurrent processes governor.) Check out SQL Server Developer edition - I think MS is dropping the price from $449 to $49 soon. The tools alone are worth that price! (Enterpise manager, Query yzer, Profiler...) -- Joe Fallon Access MVP "Mark A. Sam" <msamPlan-It-Earth.Net> wrote in message news:evh6IdIWDHA.1280tk2msftngp13.phx.gbl... > I found out on the Microsoft site that MDSE is on my Office Xp Cd. I opened > a ...

  1. #1

    Default Re: Im CoRnfused

    MSDE *is* a version of SQL Server.
    It is the exact same engine.

    It has a couple of tweaks to keep it from being used on a large scale.
    (2GB limit and 5 concurrent processes governor.)

    Check out SQL Server Developer edition - I think MS is dropping the price
    from $449 to $49 soon.
    The tools alone are worth that price! (Enterpise manager, Query yzer,
    Profiler...)
    --
    Joe Fallon
    Access MVP



    "Mark A. Sam" <msamPlan-It-Earth.Net> wrote in message
    news:evh6IdIWDHA.1280tk2msftngp13.phx.gbl...
    > I found out on the Microsoft site that MDSE is on my Office Xp Cd. I
    opened
    > a readme file which gives instructions for installing SQL Server 2000 from
    a
    > Cd. Is this a mistake or am I missing something here.
    >
    > If it is a mistake, I want to install MDSE. I'll take a chance that I
    don't
    > need instruction, but how does Access know it is there? Does it configure
    > itself to Office automatically?
    >
    > Thank.
    >
    > Mark
    >
    >

    Joe Fallon Guest

  2. #2

    Default Re: Im CoRnfused

    Thank you Joe,

    I bought a book, Access to SQL Server which seems pretty good so far and
    explained it like you did. I got it installed.

    I'm confused about a lot of things, naturally, and hope you can answer this.
    Is seems that the way to go is to use an Access Data Project rather than to
    link tables from my existing DB. I tried the linking which turned out to be
    a big mess becuase there doesn't seem anyway to manage the links.

    I created an ADP and imported the object from my existing DB into it, minus
    the queries (could I nave just converted my DB to an ADP?). Then I
    connected to the SQL database I created testing the table links. I opened a
    form copied a query from the working version. It seemed to work fine,
    howver, this project has hundreds of forms and queries. Do I need to
    recreate them all manually or is there a tool to convert the Access queries?

    Thanks if you can help me on this.

    God Bless,

    Mark



    "Joe Fallon" <jfallon1nospamtwcny.rr.com> wrote in message
    news:ecd11zKWDHA.1280tk2msftngp13.phx.gbl...
    > MSDE *is* a version of SQL Server.
    > It is the exact same engine.
    >
    > It has a couple of tweaks to keep it from being used on a large scale.
    > (2GB limit and 5 concurrent processes governor.)
    >
    > Check out SQL Server Developer edition - I think MS is dropping the price
    > from $449 to $49 soon.
    > The tools alone are worth that price! (Enterpise manager, Query yzer,
    > Profiler...)
    > --
    > Joe Fallon
    > Access MVP
    >
    >
    >
    > "Mark A. Sam" <msamPlan-It-Earth.Net> wrote in message
    > news:evh6IdIWDHA.1280tk2msftngp13.phx.gbl...
    > > I found out on the Microsoft site that MDSE is on my Office Xp Cd. I
    > opened
    > > a readme file which gives instructions for installing SQL Server 2000
    from
    > a
    > > Cd. Is this a mistake or am I missing something here.
    > >
    > > If it is a mistake, I want to install MDSE. I'll take a chance that I
    > don't
    > > need instruction, but how does Access know it is there? Does it
    configure
    > > itself to Office automatically?
    > >
    > > Thank.
    > >
    > > Mark
    > >
    > >
    >
    >

    Mark A. Sam Guest

  3. #3

    Default Re: Im CoRnfused

    Some ideas:
    1. Use linked tables at first until you get comfortable.
    Practice linking in a Test.mdb file!
    You need to create an ODBC DSN and choose the SQL Server driver and point it
    at your server.
    You will soon learn that each table in SQL Server needs a couple of things:
    A Primary Key and a Timestamp field. Timestamp is the datatype and it is NOT
    a Date! It really means RowVersion number. It is extremely useful in
    multi-user environments for detecting concurrency violations. When you do an
    Update query, Access will do a compare of every single field in the row to
    see if someone else changed the underlying data before allowing you to
    proceed. (You never see this in single user situations.) But by using a
    Timestamp field, Access only needs to compare it to the one it got when you
    first grabbed the data. If it is the same then no one else modified that
    row.

    2. Once you can link you will note that the link contains dbo_ as a prefix
    to the table name.
    That is a real pain! Your queries are based on the table name without it!
    You can manually edit them all to remove the dbo_ but that is a huge pain.
    And what if you re-link?
    You need code to handle this mess for you. (Sample code is attached below.)

    3. Once you are comfortable with linking and the code, you can try it in a
    copy(!) of your .mdb.
    Be sure to delete the Access tables that have been moved to SQL Server. This
    way the code will link to the SQL Server tables correctly and won't add a 1
    to the end of each table name.

    4. Your .mdb should work correctly once the same tables exist as linked SQL
    Server tables.
    The queries, forms reports do not need to be modified for testing.
    However, you may notice it is slower. SQL Server is better in many ways, but
    speed isn't necessarily one of them.
    Jet is extremely fast and is LOCAL. Network traffic can bog you down. Plus
    you should really look into optimizing your app to work with SQL Server and
    take advantage of its strengths. This is something you can do slowly once
    the app works as it did before you moved the tables. You can write Server
    side Functions and Stored Procedures to speed things up. You should only
    return the least amount of data needed by a user. (Combos with 10,000
    records are common in Jet because it is so fast - but it is still a really
    bad idea! Make the user enter in some criteria first to narrow the list!)

    5. Finally, A2002 upsizing wizard is an improvement over 2000 but there may
    be some issues.
    You can practice on a test .mdb to see if you like it.
    Many people skip it and just build a script for the tables themselves. But
    the one sample I tried worked fine and saved me a few hours of labor. YMMV.

    HTH
    Joe

    ==========================================
    I use this procedure to re-create links to SQL Server.
    (This eliminates the need to re-name all the tables to strip out dbo_ and it
    allows you to point to different versions of the same database easily.)
    There is a local Access table (tblODBCTables) that contains the table names
    I want to link to on the Server.
    Note: the source table name needs the dbo. prefix which is in the code. The
    linked table name usualy omits this. .

    Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
    On Error GoTo Err_LinkSQLServerTables

    Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
    Dim dbsODBC As Database, strConnect As String

    If strDSN = "" Then
    MsgBox "You must supply a DSN in order to link tables."
    Exit Sub
    Else
    strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
    strDatabase & ";"
    End If

    SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

    Call DeleteODBCTableNames

    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("tblODBCTables")
    Set dbsODBC = OpenDatabase("", False, False, strConnect)

    Do While Not rs.EOF
    Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
    tdfAccess.Connect = dbsODBC.Connect
    tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
    rs![LinkTablename]).Name
    dbs.TableDefs.Append tdfAccess
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set dbsODBC = Nothing
    Set dbs = Nothing

    Exit_LinkSQLServerTables:
    SysCmd acSysCmdClearStatus
    Exit Sub

    Err_LinkSQLServerTables:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
    & Chr(13) & Err.Description)
    Resume Exit_LinkSQLServerTables

    End Sub


    'This procedure deletes all linked ODBC table names in an mdb.
    Public Sub DeleteODBCTableNames()
    On Error GoTo Err_DeleteODBCTableNames

    Dim dbs As Database, tdf As TableDef, I As Integer
    Set dbs = CurrentDb
    For I = dbs.TableDefs.Count - 1 To 0 Step -1
    Set tdf = dbs.TableDefs(I)
    If (tdf.Attributes And dbAttachedODBC) Then
    dbs.TableDefs.Delete (tdf.Name)
    End If
    Next I

    dbs.Close
    Set dbs = Nothing

    Exit_DeleteODBCTableNames:
    Exit Sub

    Err_DeleteODBCTableNames:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
    & Chr(13) & Err.Description)
    Resume Exit_DeleteODBCTableNames

    End Sub

    --
    Joe Fallon
    Access MVP



    "Mark A. Sam" <msamPlan-It-Earth.Net> wrote in message
    news:%238gTEeQWDHA.1928TK2MSFTNGP12.phx.gbl...
    > Thank you Joe,
    >
    > I bought a book, Access to SQL Server which seems pretty good so far and
    > explained it like you did. I got it installed.
    >
    > I'm confused about a lot of things, naturally, and hope you can answer
    this.
    > Is seems that the way to go is to use an Access Data Project rather than
    to
    > link tables from my existing DB. I tried the linking which turned out to
    be
    > a big mess becuase there doesn't seem anyway to manage the links.
    >
    > I created an ADP and imported the object from my existing DB into it,
    minus
    > the queries (could I nave just converted my DB to an ADP?). Then I
    > connected to the SQL database I created testing the table links. I opened
    a
    > form copied a query from the working version. It seemed to work fine,
    > howver, this project has hundreds of forms and queries. Do I need to
    > recreate them all manually or is there a tool to convert the Access
    queries?
    >
    > Thanks if you can help me on this.
    >
    > God Bless,
    >
    > Mark
    >
    >
    >
    > "Joe Fallon" <jfallon1nospamtwcny.rr.com> wrote in message
    > news:ecd11zKWDHA.1280tk2msftngp13.phx.gbl...
    > > MSDE *is* a version of SQL Server.
    > > It is the exact same engine.
    > >
    > > It has a couple of tweaks to keep it from being used on a large scale.
    > > (2GB limit and 5 concurrent processes governor.)
    > >
    > > Check out SQL Server Developer edition - I think MS is dropping the
    price
    > > from $449 to $49 soon.
    > > The tools alone are worth that price! (Enterpise manager, Query
    yzer,
    > > Profiler...)
    > > --
    > > Joe Fallon
    > > Access MVP
    > >
    > >
    > >
    > > "Mark A. Sam" <msamPlan-It-Earth.Net> wrote in message
    > > news:evh6IdIWDHA.1280tk2msftngp13.phx.gbl...
    > > > I found out on the Microsoft site that MDSE is on my Office Xp Cd. I
    > > opened
    > > > a readme file which gives instructions for installing SQL Server 2000
    > from
    > > a
    > > > Cd. Is this a mistake or am I missing something here.
    > > >
    > > > If it is a mistake, I want to install MDSE. I'll take a chance that I
    > > don't
    > > > need instruction, but how does Access know it is there? Does it
    > configure
    > > > itself to Office automatically?
    > > >
    > > > Thank.
    > > >
    > > > Mark
    > > >
    > > >
    > >
    > >
    >
    >

    Joe Fallon Guest

  4. #4

    Default Re: Im CoRnfused

    Joe,

    I modified your delete function to rename the table links rather than
    deleting and relinking.

    Public Sub RenameODBCTableNames()

    'Function orignally named DeleteODBCTableNames
    'by Joe Fallon Access MVP
    'Microsoft Access Newsgroups
    'Modified functionality to rename Table links


    On Error GoTo Err_DeleteODBCTableNames

    Dim dbs As Database, tdf As TableDef, I As Integer
    Set dbs = CurrentDb
    For I = dbs.TableDefs.Count - 1 To 0 Step -1
    Set tdf = dbs.TableDefs(I)
    If Left(tdf.Name, 4) = "dbo_" Then
    tdf.Name = Mid(tdf.Name, 5)
    End If
    Next I

    dbs.Close
    Set dbs = Nothing

    Exit_DeleteODBCTableNames:
    MsgBox "Done"
    Exit Sub

    Err_DeleteODBCTableNames:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source &
    Chr(13) & Err.Description)
    Resume Exit_DeleteODBCTableNames

    End Sub


    I like to keep things simple to match my mind ;)

    God Bless,

    Mark


    Mark Guest

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139