Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: Access 97/ODBC connectivity issues

    At work, I'm developing a MSSQL back end for an Access database. The data used to be held on Access 97, but I moved the data to SQL Server 2000 because we had over 100,000 rows and too many users and Access's performance was really starting to degrade.

    I'm accessing the info on SQL Server through directly-linked tables...we're not linking with VBA/DAO. We're not using ADO and Access Projects aren't an option; apparently that's not a feature in Access 97. All the old queries have been re-written in T-SQL and encapsulated as stored procedures on the SQL Server so they run more quickly. We access these stored procedures via pass-through queries, not DAO.

    During development, I simply set up a machine DSN which wrote an entry to my registry, because it was just EASIER than futzing around with a SQL app user or a file DSN, especially since our network at work is twitchy and my file DSNs kept disappearing. ODBC connectivity wasn't a problem during development, but now that we're in the testing phase we've discovered a major problem. Nobody can connect to the SQL Server behind Access except for myself and one of my colleagues. Every time the others try to connect, they get an ODBC error.

    We fiddled around with DSN-less connection strings for a while before we decided to try the obvious: could we set up machine DSNs for the users having ODBC problems? I suspected that the SQLSVR32.DLL might not have been present on their machines, since apparently the SQL Server ODBC driver isn't part of an Access 97 typical install (you have to choose the custom option at installation to get it, according to Microsoft) and you know any company's network people will choose the easiest, laziest option for installing software. We discovered that we could not create any machine DSN connections to the SQL Server on these peoples' machines; it appears that the driver simply isn't there, or has some other problem. On one guy's machine, the SQLSVR32.DLL is present but it has no information for it on the Drivers tab on odbcad32.exe. Eh?!?!?!?!

    Am I missing something REALLY painfully obvious here, or is everyone else's Access 97 install messed up? I'm new to this; this is my first gig doing DB development and I'm not actually a programmer, I'm just OK at writing T-SQL. I'm a total VBA newbie.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're remarkably thorough in your description for not being a programmer...

    Sounds like you're on the right track.

    I had an issue a while back and used a method very similar to the one described here. I would wager you do indeed have an issue with not having SQLSRV32.dll registered.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Teddy
    You're remarkably thorough in your description for not being a programmer...
    Hehehe, this is my first gig in I.T. outside of QA. I did QA for 8 years, where being anally precise is kind of required!

    Sounds like you're on the right track.

    I had an issue a while back and used a method very similar to the one described here. I would wager you do indeed have an issue with not having SQLSRV32.dll registered.
    Hmmm, I'll definitely give that a try. I'm not a VBA head, but one of the other guys on the team is. Hopefully we can use that, though the way our PCs are set up is very restrictive. The stuff we've done with the ODBC drivers is just guesswork since we can't even execute ODBCad32.exe on anyone else's PC. None of our other users have access to their C drives or the Run utility, so who knows if we can even write to their registries! I'll definitely try that tho (on Monday) and let you know how it goes!

  4. #4
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by disruptivehair
    At work, I'm developing a MSSQL back end for an Access database. The data used to be held on Access 97, but I moved the data to SQL Server 2000 because we had over 100,000 rows and too many users and Access's performance was really starting to degrade.

    I'm accessing the info on SQL Server through directly-linked tables...we're not linking with VBA/DAO. We're not using ADO and Access Projects aren't an option; apparently that's not a feature in Access 97. All the old queries have been re-written in T-SQL and encapsulated as stored procedures on the SQL Server so they run more quickly. We access these stored procedures via pass-through queries, not DAO.

    During development, I simply set up a machine DSN which wrote an entry to my registry, because it was just EASIER than futzing around with a SQL app user or a file DSN, especially since our network at work is twitchy and my file DSNs kept disappearing. ODBC connectivity wasn't a problem during development, but now that we're in the testing phase we've discovered a major problem. Nobody can connect to the SQL Server behind Access except for myself and one of my colleagues. Every time the others try to connect, they get an ODBC error.

    We fiddled around with DSN-less connection strings for a while before we decided to try the obvious: could we set up machine DSNs for the users having ODBC problems? I suspected that the SQLSVR32.DLL might not have been present on their machines, since apparently the SQL Server ODBC driver isn't part of an Access 97 typical install (you have to choose the custom option at installation to get it, according to Microsoft) and you know any company's network people will choose the easiest, laziest option for installing software. We discovered that we could not create any machine DSN connections to the SQL Server on these peoples' machines; it appears that the driver simply isn't there, or has some other problem. On one guy's machine, the SQLSVR32.DLL is present but it has no information for it on the Drivers tab on odbcad32.exe. Eh?!?!?!?!

    Am I missing something REALLY painfully obvious here, or is everyone else's Access 97 install messed up? I'm new to this; this is my first gig doing DB development and I'm not actually a programmer, I'm just OK at writing T-SQL. I'm a total VBA newbie.
    Quick question that might address your problem. DId you use System DSN where every user in the netwrok can see when you created your DSN connection?

  5. #5
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by fredservillon
    Quick question that might address your problem. DId you use System DSN where every user in the netwrok can see when you created your DSN connection?

    A system DSN can only be seen by people who log onto the workstation that created it; at least that's the impression that I got, since system and user DSNs are written to the registry. We tried using file DSNs, but they keep getting lost.

  6. #6
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Quote Originally Posted by disruptivehair
    A system DSN can only be seen by people who log onto the workstation that created it; at least that's the impression that I got, since system and user DSNs are written to the registry. We tried using file DSNs, but they keep getting lost.
    I have written quite a few standalone vb applications in a server/client environment in our multi sites institutions. One I have, believe it or not a significantly large server MS Access 2000 backend database of 300 Meg in size (One table has about 400,000 rows). I don't even bother setting up the DSN of the server because I don't have Admin rights to it, but I have read/write to the database directory in the server. So I use this code in my routine which is in the module and never failed me.

    Code:
    Public Sub OpenCOnnection()
    CNN.Provider = "Microsoft.Jet.OLEDB.4.0"
    CNN.Open "Data Source=" & ServerPath & ServerDatabaseName
    End Sub
    When my application main form loads I would fetch the Serverpath and ServerDatabaseName from a text file and call the OpenConnection routine above and it works just fine.

    The server Path for example is "\\ServerName\Apps\MyData\"
    and the ServerDatabaseName would be = "MSData.mdb"

    I hope this proven codes would give you hope for your solution.
    Using good sql technique with MS Access 2000 can retrieve records within a second even with multiple concurrent users in a huge database. I also developed and used my method to prevent user lock-up which is inherent to MS Access as a result users preferred to use my front-end rather than the vendor's because it's about 700 times faster and customized to their needs and also minimized if not prevented erroneous entries.
    Last edited by fredservillon; 01-15-06 at 02:02.

  7. #7
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by fredservillon
    I have written quite a few standalone vb applications in a server/client environment in our multi sites institutions. One I have, believe it or not a significantly large server MS Access 2000 backend database of 300 Meg in size (One table has about 400,000 rows). I don't even bother setting up the DSN of the server because I don't have Admin rights to it, but I have read/write to the database directory in the server. So I use this code in my routine which is in the module and never failed me.

    Code:
    Public Sub OpenCOnnection()
    CNN.Provider = "Microsoft.Jet.OLEDB.4.0"
    CNN.Open "Data Source=" & ServerPath & ServerDatabaseName
    End Sub
    When my application main form loads I would fetch the Serverpath and ServerDatabaseName from a text file and call the OpenConnection routine above and it works just fine.

    The server Path for example is "\\ServerName\Apps\MyData\"
    and the ServerDatabaseName would be = "MSData.mdb"

    I hope this proven codes would give you hope for your solution.
    Using good sql technique with MS Access 2000 can retrieve records within a second even with multiple concurrent users in a huge database. I also developed and used my method to prevent user lock-up which is inherent to MS Access as a result users preferred to use my front-end rather than the vendor's because it's about 700 times faster and customized to their needs and also minimized if not prevented erroneous entries.
    Would that code even work in Access 97 though? Using Access 2000 isn't an option.

  8. #8
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Quote Originally Posted by disruptivehair
    Would that code even work in Access 97 though? Using Access 2000 isn't an option.
    Yes, but you have to use the Jet3.51 instead of 4.0. MS Jet 4.0 will not work with MS Access 97 efficiently. I'm talking from experience.

    So with MS 97 Access Database use this code instead.
    Code:
    Public Sub OpenCOnnection()
    CNN.Provider = "Microsoft.Jet.OLEDB.3.51"
    CNN.Open "Data Source=" & ServerPath & ServerDatabaseName
    End Sub
    Bear in mind that if you upgraded to Office 2000 in the local computers, the MS Jet 4.0 will preside over the MS Jet 3.51 and your data retrieval speed from Access 97 would suffer. In this case you'll have to remove MS Jet 4.0 and install back MS Jet 3.51. If you're still using Office 97, your OK.

    If your Access 97 gets corrupted and you can't open the MS Access database to run the RepairCompact utility, I can give you the code to repair without opening the database.

  9. #9
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by fredservillon
    Yes, but you have to use the Jet3.51 instead of 4.0. MS Jet 4.0 will not work with MS Access 97 efficiently. I'm talking from experience.

    So with MS 97 Access Database use this code instead.
    Code:
    Public Sub OpenCOnnection()
    CNN.Provider = "Microsoft.Jet.OLEDB.3.51"
    CNN.Open "Data Source=" & ServerPath & ServerDatabaseName
    End Sub
    Bear in mind that if you upgraded to Office 2000 in the local computers, the MS Jet 4.0 will preside over the MS Jet 3.51 and your data retrieval speed from Access 97 would suffer. In this case you'll have to remove MS Jet 4.0 and install back MS Jet 3.51. If you're still using Office 97, your OK.

    If your Access 97 gets corrupted and you can't open the MS Access database to run the RepairCompact utility, I can give you the code to repair without opening the database.

    How does this code work for connecting to SQL Server, though? That's what I'm trying to do.

  10. #10
    Join Date
    Oct 2005
    Posts
    178

    Question

    Quote Originally Posted by disruptivehair
    How does this code work for connecting to SQL Server, though? That's what I'm trying to do.
    I'm not sure why we're hopping between SQL and MS Access. Which one are you trying to connect to?

    for SQL Server you use the SQLOLEDB provider like
    Con.open "PROVIDER=SQLOLEDB;DATA SOURCE=ServerName;UID=uid;PWD=pw;DATABASE=sqldb"

    Maybe you can give more info on what you have and what you want to accomplish.
    Last edited by fredservillon; 01-16-06 at 10:54.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SQL Server Connection

    See my post: http://www.dbforums.com/t1207303.html which has a file called: updateDSNs.mdb you can download to automatically create a system DSN on another user's machine (provided they have the privelages to do so - if not, login as admin.) Just replace the linked tables in the mdb with those you want DSNs created for and run it on the user's machine. If you get an error when running the mdb, then I'd check to see that MDAC and the ODBC Connectivety is installed on their machine. When I connect to the database for linked tables, I use the SQL Server driver in ODBC. Just make sure the "Save Password" is checked when you link in the tables.

    Also, make sure the user's login is in SQL Server security and they are also in as a user to the database (and any roles you may have created.)

    Hope that helps.
    Last edited by pkstormy; 01-16-06 at 13:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by pkstormy
    See my post: http://www.dbforums.com/t1207303.html which has a file called: updateDSNs.mdb you can download to automatically create a system DSN on another user's machine (provided they have the privelages to do so - if not, login as admin.) Just replace the linked tables in the mdb with those you want DSNs created for and run it on the user's machine. If you get an error when running the mdb, then I'd check to see that MDAC and the ODBC Connectivety is installed on their machine. When I connect to the database for linked tables, I use the SQL Server driver in ODBC. Just make sure the "Save Password" is checked when you link in the tables.

    Also, make sure the user's login is in SQL Server security and they are also in as a user to the database (and any roles you may have created.)

    Hope that helps.
    I appreciate all the advice Paul but unfortunately we can't get it to work.

    The way our machines are set up, we can't even see the C drives on our colleagues' machines. We can't run odbcad32.exe and we can't get any information on how Access was installed; some of them don't even have Access per se, they just have the Access 97 Runtime utility. We don't have any admin privileges at all; we're lucky that we can even see the C drives on our own PCs. Not ideal for development!

    We also can't run the code to manually register the SQLSVR32.DLL file unless we want to be fired. At this point we will just have to wait for the powers-that-be to give the IT people (who we are not) permission to fix it. Forms will have to be filled in, and it will probably take weeks. Why do companies do this???

  13. #13
    Join Date
    Oct 2005
    Posts
    178

    Cool

    Quote Originally Posted by disruptivehair
    I appreciate all the advice Paul but unfortunately we can't get it to work.

    The way our machines are set up, we can't even see the C drives on our colleagues' machines. We can't run odbcad32.exe and we can't get any information on how Access was installed; some of them don't even have Access per se, they just have the Access 97 Runtime utility. We don't have any admin privileges at all; we're lucky that we can even see the C drives on our own PCs. Not ideal for development!

    We also can't run the code to manually register the SQLSVR32.DLL file unless we want to be fired. At this point we will just have to wait for the powers-that-be to give the IT people (who we are not) permission to fix it. Forms will have to be filled in, and it will probably take weeks. Why do companies do this???
    Let me ask you this and we will go step by step to see which and what wall you are hitting.

    1. Can you browse to the directory where your MS Access database resides?
    or do you have a virtual path to your database. or can you click on the network neighborhood or "My Network Places" down to the "Entire network" Icons. Can you right-click on an icon in your desktop that you know is part of your database or application and see the properties?

    If you have an idea that you think would help evryone's life at work easier amd more productive, you can never get fired even if you violated a policy or two as long as security violation is not involved. If your IT group don't have the resources and you have it then offer it. Create a small independent network and prove your application works and then have the IT people with administrative rights to install it for you.
    I have done it.
    Last edited by fredservillon; 01-16-06 at 21:28.

  14. #14
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by fredservillon
    Let me ask you this and we will go step by step to see which and what wall you are hitting.

    1. Can you browse to the directory where your MS Access database resides?
    or do you have a virtual path to your database. or can you click on the network neighborhood or "My Network Places" down to the "Entire network" Icons. Can you right-click on an icon in your desktop that you know is part of your database or application and see the properties?

    Yes, but that doesn't matter; we don't have any trouble connecting to MS Access. The trouble we're having is with ODBC connectivity to SQL Server, since we're working with linked tables and pass-through queries in Access. The data actually lives on SQL Server now.

    If you have an idea that you think would help evryone's life at work easier amd more productive, you can never get fired even if you violated a policy or two as long as security violation is not involved. If your IT group don't have the resources and you have it then offer it. Create a small independent network and prove your application works and then have the IT people with administrative rights to install it for you.
    I have done it.

    I wish I could agree with that, but my company is an exception. I know someone who was given a formal written warning for admitting that he had used a USB pen drive to store files so he could work on them at home. I know a team who has been waiting for 7 months to have their ODBC driver issues resolved, too!

  15. #15
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by fredservillon
    Let me ask you this and we will go step by step to see which and what wall you are hitting.

    1. Can you browse to the directory where your MS Access database resides?
    or do you have a virtual path to your database. or can you click on the network neighborhood or "My Network Places" down to the "Entire network" Icons. Can you right-click on an icon in your desktop that you know is part of your database or application and see the properties?

    Yes, but that doesn't matter; we don't have any trouble connecting to MS Access. The trouble we're having is with ODBC connectivity to SQL Server, since we're working with linked tables and pass-through queries in Access. The data actually lives on SQL Server now.

    If you have an idea that you think would help evryone's life at work easier amd more productive, you can never get fired even if you violated a policy or two as long as security violation is not involved. If your IT group don't have the resources and you have it then offer it. Create a small independent network and prove your application works and then have the IT people with administrative rights to install it for you.
    I have done it.

    I wish I could agree with that, but my company is an exception. I know someone who was given a formal written warning for admitting that he had used a USB pen drive to store files so he could work on them at home. I know a team who has been waiting for 7 months to have their ODBC driver issues resolved, too!

Posting Permissions

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