Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: VB to SQLServer

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

    VB to SQLServer

    I am preparing to write a small app to get data from one of my clients' databases, format it and squirt it out as a text file. The problem is, they're running SQLServer, and I have a sneaking suspicion that it won't be quite as simple as reading a Jet db, am I right?

    Up till now I've stuck with DAO, but reading up leads me to the conclusion that this is something I'll have to do with ADO and OLEDB connectivity. Fine. The question is, will I have to run SQLServer *here* in order to test my code? I'm hoping that there is a way to write the app for a Jet/Access db, connected by OLEDB, and then just change the connection/driver/etc when it's ready to go at my clients'.

    The other point is that I have VB5 Pro. My intention was to wait for VB.NET to come out to avoid having to upgrade *twice*. Two questions therefore: could I get this going as a first working usable thing by coding with ADO in Access2000 (and then change to VB.NET later), and will I need VB Enterprise or Office Developer? Frankly, I can't see this thing being financially viable if I need more than just VB6/7 Pro and/or straight Access.

    Sorry, too many questions, but I think I need to know what I'm up against before I start, and despite 8 years with VB and 15 with databases I've never needed to dip my toes into the client/server maelstrom. Until now.

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to SQLServer

    Yes, move to ADO. It's too EASY.

    You are correct that you can use Jet local and simply change your connection parameters to SQLServer -- one line of code:

    cn.Open "Provider=MSDASQL;Driver={SQL Server};Server=COMPANYSQL;Uid=sa;Pwd=;Database=BIG DB"
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: VB to SQLServer

    That's great... many thanks. I thought it *should* be simple, I just needed someone who'd been there already to confirm it. So ODBC does work after all.

    And I don't need VB Enterprise or ODE? (I'm just thinking of OLEDB driver availability &c.)

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

    Re: VB to SQLServer

    I agree that you should move to ADO, but it isn't necessarily easy even if the object model is deceptively simple.

    If you're using Access 2000, you already have the MSDE, which is SQL Server optimized for 5 seats and without the management console that comes with the full SQL Server. If you build the application against that, you won't have to worry about changing it from Jet to SQL Server, and you won't have any unpleasant surprises because of the significant differences between the two (like the wildcards they use, and their date delimiters, among other things).
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to SQLServer

    If all you clients are at least Win9x w/ IE 4.0 +, they've got the dll (either MSADO15.dll or MSADO21.tbl), so you don't need to worry about the PCs either.

    The real beauty is the MSDE in Access 2000. Same engine as SQL server so your SQL syntax (Transact-SQL as it used to be called -- maybe still is) is the same. You only have to change the provider and server parameters.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: VB to SQLServer

    Kevin, Charlotte, many thanks. So I shall be starting to develop an Access "project" with MSDE. The faint tinkling sound you heard was that of the scales falling from my eyes as I finally understood that MSDE is kind-of Baby SQLServer. Lovely lovely lovely. Hope you don't mind if I come back next week when I can't get it to work [img]/forums/images/smilies/smile.gif[/img]

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

    Re: VB to SQLServer

    You're welcome to come back with more questions, but we do charge additional fees for repeat customers! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Have fun.
    Charlotte

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

    Re: VB to SQLServer

    Surprise surprise, I have problems [img]/forums/images/smilies/smile.gif[/img] Sorry... After fiddling on and off for days with the ODBC Data Source Administrator, trying to set up a DSN and getting network errors (Huh?) I realised this morning that perhaps I didn't have MSDE/SQLServer actually installed. Eventually I found a likely sqlsetup.exe on the Office CD, and lo! now SQLServer is running on my PC. So far, so hoopy. Now when I try to test the DSN I just get 'login failed for user james' errors. How do I set up user accounts for SQLServer then (and are they necessary)? I can't even play with ADO yet as I can't connect to anything. [img]/forums/images/smilies/sad.gif[/img]

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

    Re: VB to SQLServer

    the default administrator login is sa with no password. Try that.
    Charlotte

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

    Re: VB to SQLServer

    Thanks... 'sa' wasn't exactly obvious! Once I tried that, I was able to successfully test the DSN, but I still couldn't get ADO to open a connexion. I've kept coming back to this over the last few days (whenever 1. I could face it and 2. I had time), and this afternoon I tried again, having realised that the database name was not the same as the project name, but had 'SQL' tagged on the end by default. Hey presto, a working connexion and data being printed to the Debug window. So that's a start. My next task is to see if I can also open a connexion to an MDB from this project at the same time.

    Here's the idea: I want to write a program (initially in Ac2000, later to be in VB6 or VB.NET) which I can use to simultaneously read from an SQLServer db AND read/write an MDB. So should my initial Ac2000 app be an MDB or a 'project'? Or am I heading down the wrong path entirely?
    TIA!

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

    Re: VB to SQLServer

    It depends on what exactly you want to do. You can work with more than one connection, although they're easier to manage in VB than in Access because in Access you can only add a data environment when you create a new database. ADP are made to be front-ends to SQL Server back ends, so they're very different from MDBs. It may very well be possible to create a connection to an MDB from an ADP, but if you do, you still won't be able to see the tables or relate them to the SQL Server tables, only work with them through ADO connections in VBA.

    What is the purpose in working with both types of back end? If you want to move data from SQL Server to Access, use the DTS that comes with SQL Server, including MSDE, to transfer the data.
    Charlotte

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

    Re: VB to SQLServer

    Thanks for replying. What I need to do is create an app (initially Ac2000, later VB6) which:
    1. Connects to a customer's SQLServer *OR* Access db to read data from a product database (some customers are using SQLServer already, others do not need anything more than a smallish Access mdb - I cannot force them all to use SQLServer)

    2. Stores its own working metadata in an mdb: settings, project information etc

    This is in order to write catalogues/reports to plaintext/Word/Quark files. I have to have my own db to keep my app's info in, and get the actual data from whatever the customer's system already uses.

    So you can see why I need 2 connexions. If only they all used Access not SQLServer, I could just make 2 DAO connexions!!

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

    Re: VB to SQLServer

    If the back end may not be SQL Server, then you have no choice but to create an MDB. I've built databases where all the tables in the backend are accessed only through ADO connections with no links in the front end, so I know it's possible to do this. Your greater problem will be to write your code so that it can correctly create the connection to either source, since you can't use the Jet OLEDB provider with SQL Server, you have to use a different provider and all the syntax for the connection and its properties change.
    Charlotte

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

    Re: VB to SQLServer

    That'll be OK, as I'll be lovingly hand-crafting specific versions for each customer anyway. It doesn't need to be self-adapting. Just so long as I know the correct connection strings.

    I now have an MDB with code in a form which lets me read data from the SQLServer db, which I'm pretty pleased about. My next question, therefore, should be the very simple bit: how do I connect to an MDB (either the CurrentDB, as DAO always called it, or a file separate from the db with the code in) via ADO? Presumably it's just a change to the connexion string. Right now I have:

    cnn.Open "database=adp1SQL;provider=sqloledb;Trusted Connection=False;UID=sa;pwd=;"

    to get into the SQLServer db. I'm trying:

    Zcnn.Open "database=testdatafile;provider=sqloledb;UID=admin ;pwd=;"

    to connect to (the current) MDB, but all I get is 'login failed for user "admin"' errors, and from my experiences over the last few days with SQLServer I suspect it's the database and not the user it can't find. I feel like I'm nearly there now though, with your excellent help.

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

    Re: VB to SQLServer

    The provider for Jet is "Microsoft.Jet.OLEDB.4.0", not SQLOLEDB.
    Charlotte

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
  •