Results 1 to 14 of 14

Thread: SQL connection issue

Hybrid View

  1. Dec 8th, 2004, 01:22 PM #1
    JPnyc
    JPnyc is offline

    Thread Starter
    PowerPoster JPnyc's Avatar
    Join Date
    Oct 2002
    Location
    Manhattan
    Posts
    3,015

    SQL connection issue

    I have a painfully simple app I wrote for in-house use here at work, and it functioned perfectly until we swapped out the server housing the SQLserver DB. I changed the servername in the system DSN (and also tried creating a new one) on the station running the app (mine) but the app no longer makes connection. The DSN successfully connects according to the test, but the app crashes.

    Since the old server ran 2000 server, and the new one 2003 server, I thought perhaps there's an updating of provider required. Here's what I'm using:

    cnSPR.Provider = "Microsoft.Jet.OLEDB.4.0;"
    cnSPR.ConnectionString = "Provider=MSDASQL;DSN=SPR;"

    That worked fine, up until now. Any help appreciated.
    Reply With Quote Reply With Quote
  2. Dec 8th, 2004, 01:26 PM #2
    RobDog888
    RobDog888 is offline
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Re: SQL connection issue

    Your provider is showing Access. Try this site for a complete listing of ADO / SQL connection strings.

    Connection String
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
    Reply With Quote Reply With Quote
  3. Dec 8th, 2004, 01:53 PM #3
    JPnyc
    JPnyc is offline

    Thread Starter
    PowerPoster JPnyc's Avatar
    Join Date
    Oct 2002
    Location
    Manhattan
    Posts
    3,015

    Re: SQL connection issue

    Quote Originally Posted by RobDog888
    Your provider is showing Access. Try this site for a complete listing of ADO / SQL connection strings.

    Connection String
    Unfortunately, that didn't solve the problem either. There is a network/firewall issue afoot here, I'm sure of it. We tried to create a new system DSN using 2 different logins that are known to exist and be correct, yet both logins failed during the DSN creation process. Only using windows authentication, can we create a DSN, but that still doesn't allow us into the DB. Our network admin is working on it. Thanks for your reply!
    Reply With Quote Reply With Quote
  4. Dec 8th, 2004, 06:24 PM #4
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL connection issue

    A customer of mine just had this problem - upgrade from 2000 to 2003 would not allow links to the DB anymore.

    It was because they did not upgrade the server to MDAC 2.8 before the 2003 upgrade.

    This left multiple copies of .DLL's for MDAC in place - bad versions.

    There is a MSDN knowledge base article about it - it's more about EXCHANGE, but the same issue is related to SQL.

    Try searching MSDN for MDAC and 2003 - maybe that will work...

    I just searched and found this bit of bad news for you:

    No more JET in MDAC 2.6 and above

    Like RD asked - why are you not using the proper provider - you should use SQLOLEDB - make that change before you go any further.
    Reply With Quote Reply With Quote
  6. Dec 8th, 2004, 06:31 PM #5
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL connection issue

    Here's the KB article about upgrading a 2000 to 2003 server that has MDAC 2.7 on it.

    You'll find a problem with Oledb32.dll - check it out...

    MDAC problem with 2003 upgrade
    Reply With Quote Reply With Quote
  7. Dec 8th, 2004, 06:50 PM #6
    JPnyc
    JPnyc is offline

    Thread Starter
    PowerPoster JPnyc's Avatar
    Join Date
    Oct 2002
    Location
    Manhattan
    Posts
    3,015

    Re: SQL connection issue

    You guys ROCK. I'm home now, but I'll check on that 1st thing tomorrow. Mille grazie, lads.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width