Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link to SQL-Server table with ADOX (Ac2000 SR1)

    I'm attempting to link to a table in an SQL-Server db. If linking to an MDB I use DAO (Set Tdf = db.CreateTableDef("table1") // Tdf.Connect = strConnect // Tdf.SourceTableName = "sourcetable" // db.TableDefs.Append Tdf). I now want to programmatically set up a link to a named SQL-Server table using a similar method, but I presume I need to use ADOX. I have a method from the MSDN Library, but it fails to even connect to the current MDB with "Could not find installable ISAM" when running the following code:
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "database=try.mdb;provider=Microsoft.Jet.OLEDB.4.0 ;UID=admin;pwd=;"
    I can do normal read/write stuff to SQL-Server via ADO, but haven't needed to *link* like this before, so am a bit stuck. Ideas?

    [sorry if this post appears twice - my ISP dropped the connexion after I clicked Post]

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

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    If you are working with SQL Server tables, you are always linking, whether you DAO or ADO. Unless I misunderstand your question, if you've done it before using ADO, then you've done it already. One of the fundamental differences between ADO and linked tables with DAO is that they are available only as long as the connection is active. ODBC linked tables (what you use with DAO) are available until you unlink from them either programatically by deleting the TableDef, or by manually deleting them. If this doesn't make any sense (I'm operating on about 4 hours of sleep), please repost with more details about what you are trying to do using ADO.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    You don't create links with ADO, you create connections. With DAO/ODBC you actually have a durable physical link to a back end table, but that isn't what happens with ADO.

    Here's a representative connection string from SQL Server, assuming you're logging in as sa rather than using NT authentication:

    "driver={SQL Server};server=NOTEBOOKSQL2K;uid=sa;pwd="

    Here's one for Access:

    "Provider= Microsoft.Jet.OLEDB.4.0;Data Source = CataMyData.mdb"

    Try setting the ConnectionString property of the connection object to your string and then set the activeconnection property of the catalog to the connection object and see if that makes a difference.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    You may have *possibly* misunderstood my question, but you answered it anyway [img]/forums/images/smilies/smile.gif[/img]

    I can make connections to Jet or SQL-Server tables via ADO, no problem, and read and write data. Have done that on other projects. I'd better explain what I want to do and why.

    The matter in hand is a shared Ac2000 db which is being overstressed, with data corruption the result. There's an mdb with the forms and reports installed on each PC, which when opened uses DAO to set up linked tables to a data-only mdb on the server (db.tabledefs.append tdf, etc etc.). These linked tables are dropped when the app exits.

    Due to the corruption, I want to put SQL Server on the server and replace the shared Jet mdb with something more robust. I considered (and tried) converting the app to an Access Project using the upsizer, but there are crosstable queries and the wizard refused to convert them. Then a friend suggested I simply replace the links to the data mdb with links to the SQL Server db... which is where we came in. I was under the impression that DAO couldn't be used with SQL Server dbs, which is why I was trying to replicate the setting-up-linked-tables-with-DAO bit using ADO/ADOX. If I understand your replies folks, you can't do that with ADO, cos the *connection* (which is temporary) is what gets the data, not the link (as one can set up, permanently, either with DAO or using the Access GUI to Link Tables).

    I guess I'm trying to work out the best way to simply convert the data from Jet to SQL Server without having to rewrite the app much. Can I still use ODBC linked tables, or is that likely to cause more instability grief? I want this app to be solid. Maybe the answer is an adp, but I don't know anyone who's used them in anger.

    James

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Thanks Charlotte, your first para answered my question... but I still have the problem. Please see my reply to Wendell in this thread.

    James

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

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Well, in my view (Charlotte and others may disagree), the simplest way to convert is to simply create an ODBC connection to the SQL database and keep right on using DAO. Doing it that way involves little or no changes to the front-end (queries, forms, reports and code), and things work pretty much as they did with a Jet back-end. In fact the SQL upsizing wizard does a reasonable job in many cases - in some it is flakey and will refuse to upsize a table for no apparent reason. The downside is that performance isn't optimal, though it is normally considerably faster than Jet for any significant number of users. In addition, every object that is bound to a table will require two connections - no idea why. That makes using ODBC connections using the MSDB engine unusable for more than one or two users. There are a few subtle differences that will trip you, such as the fact that auto-numbers are assigned when you start a new record in Jet, and at the time the record is saved in SQL Server. In any event, your impression that DAO cannot be used with SQL Server tables is incorrect. We've done it that way for years.

    On the other hand, you can use ADO and not go to an ADP structure. We do that occasionally even for Jet tables for some arcane reasons. In general with SQL tables the performance will be better, and the connection overhead is less. But it does involve learning a new methodology, and there are some things that are either a real pain or can't be done at all with the Access2K version of ADO that DAO handles in a pretty straightforward fashion. Regardless of which path you take, you will benefit substantially from a performance and a stability perspective. Since you have an existing app and don't want to redesign it from the ground up, DAO will be the quickest and easiest way to make the switch.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    I don't disagree at all, Wendell. If the purpose is to salvage as much of the existing Access app as possible, then that's the logical way to do it. I tend to use a mixture of ADO and DAO in my mdbs, and the only thing I might point out is that you truly have to install one of the updated versions of MDAC (2.5 or 2.6 would be my recommendation) to work with ADO in Access 2K, since the 2.1 built in is fairly useless.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Indeed. I wrote a (non-Access) VB app last year which reads and writes a Jet db for its own purposes via DAO and reads an SQL Server db via ADO. Glad to know I'm not alone in combining the two [img]/forums/images/smilies/smile.gif[/img] I ship MDAC 2.5 with it.

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Wendell, many thanks. I'm happy to discover that I was wrong about the DAO/SQL server combination.

    You say that the MSDE is unusable for >2 users when using DAO, and that clarifies another point: it's optimised for 5 connexions I understand, but I didn't know whether we'd get away with eight users. Evidently not. So far I've only used it on my laptop for one user (me).

    In fact, *recoding* for ADO isn't a main issue here: the app is small, only 2 forms of any consequence and one major table, and apart from where I set up several linked tables there are only 3 places where I've used DAO anyway (in each case, to read the last-used value of a counter from a settings table). All stuff I've done in ADO before, and arguably I should've here in the first place! So recoding that wouldn't be a pain. It was the dozen-or-more reports, based on queries (none of which upsized) which I didn't want to have to rebuild. Ugh. So I'm glad that the DAO route is doable.

    Right then: just the main problem: getting the DAO link to work. I don't understand the DSN bit. This is what I'm trying:

    Set db = CurrentDb()
    strConnect = "ODBC;DATABASE=dbname;UID=sa;PWD=;DSN=dbname;"
    Set Tdf = db.CreateTableDef("tblLinked")
    Tdf.Connect = strConnect
    Tdf.SourceTableName = "sourcetable"
    db.TableDefs.Append Tdf
    Set Tdf = Nothing
    db.Close

    But of course without defining a DSN, which I don't know how to do, I'm stuck and just get ODBC connect failure errors. Suffice to say that I'm very grateful for your advice.

    James

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

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    To create ODBC connections, you go to Control Panel and choose ODBC Connections, 32 bit ODBC connections, or Administrative Controls and then Data Connections (win2000) depending on the verison of your OS. You say Add New, tell it you want a SQL Server connection, and then specify the details about the server and test it. That sets up the DSN.

    Then you go to the MDB file and say you want to Get External Data / Link and specify ODBC for the type of document you want to connect to. That brings up a dialog box listing your ODBC DSNs, and you choose the one you defined above. That will then list the tables (and views if you have any) that are in the SQL Server database, and you pick the one or ones you want. One slightly ugly part is that you will see that "dbo_" has been prepended to each table or view name. We usually rename them to get rid of that so the name you were using in Access is the same. Hope this helps.
    Wendell

  11. #11
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Thank you so much, your help has been invaluable. I had been so close, but just couldn't work out the DSN bit. All is now connected and working... just some minor oddities to clear up (as you warned).

    I'm presuming that the DSN only has to be set up on the server (it's all the same on my development machine, which runs everything).

    Tell me though, have you ever had any problems with data corruption on SQL Server?

    James

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

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    __________________________________________________ ____________
    I'm presuming that the DSN only has to be set up on the server (it's all the same on my development machine, which runs everything).
    __________________________________________________ ____________

    Unfortunately, you do have to set up the DSN on each workstation that connects to the database. When you work on the same machine you have to do it on the server, but normally you wouldn't set up a DSN on the server.

    As to data corruption on SQL Server - hardly ever. In 8 years we have had one corruption, and that was attributed to a bonehead testing situation where the drives in a software RAID arrangement were taken out and then put back in a different physical position. I should hasten to caution you however, that you do need to use the SQL maintenance tools religiously. We do SQL DBCC checks and repairs weekly on every SQL database, and we rebuilt the indexes on the same schedule. Bottom line - SQL Server is pretty much rock solid and runs for weeks at a time without restarts or intervention, and offers dramatically better multi-user performance as long as you have a resonably robust server with solid hardware. But it costs more too!
    Wendell

  13. #13
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Thanks for the advice. Looks like the client will be getting Small Business Server (but not using all of it)... and I may get SQL Server Dev Ed (for familiarisation purposes). Yum.

  14. #14
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Well, after all the help I've received here, I have a working system, connecting to the SQL-Server db with DAO Linked Tables. Tomorrow I shall visit my customer to set up a small trial with 2 client PCs connecting to MSDE, to check it all works before they shell out for full-blown SBS.

    Question: how do I copy an existing SQL Server db from my PC to another? If I just copy the MDF and LDF files to the MSSQLData folder, I suspect that they won't appear in the list of databases that Access can connect to... (This from tests on my PC and making copies of files.) I have only MSDE.

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

    Re: Link to SQL-Server table with ADOX (Ac2000 SR1)

    Unfortunately, your suspicions are correct. You can't simply copy the data and log files and make things appear suddenly on the other PC. The only reasonable scenario I can suggest would be to copy the tables to an mdb file, transport it to your customer, create a small ADP to create the new tables, and then import the data into the new tables. Unfortunately, I can't test your situation at the moment, as I am running a PC that has AccessXP and SQL Server Developer installed.

    Perhaps someone else can come up with a better suggestion.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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