Questions

Using MS Access and ODBC to access an SQL Database

Tags:
+
0 Votes
Locked

Using MS Access and ODBC to access an SQL Database

yabbayogi
I have an MS Access 2000 database running on a Win XP Pro workstation that was pulling data from an external dBaseIII-format database using ODBC. That database has now been replaced with an MS SQLExpress database on an MS 2000 Small Business Server. I have re-established the external database links in the Access database using "Machine Data Source" SQL-ODBC links, and I can see the SQL data and run queries on it. But whenever I try to run an Access Visual Basic module from within the MS Access database (either a function I've written or even adding a new button to a form), Access throws up on me with a "Error accessing file. Network connection may have been lost" message. This is even for functions that don't look at the SQL data! From that point on, my Access database becomes unstable, and starts acting crazy (e.g. not letting me delete queries, or no longer displaying forms, etc), and I have to abandon it for a previous version of the Access database. Any ideas from anyone would be most appreciated on how I can write, compile, and run my much-needed VB Access functions, especially those that do look at the external SQL data.
  • +
    0 Votes
    yabbayogi

    A final note on my question. Visual Basic functions that exist in the MS Access database from its "dBaseIII" days still work. But any *new* VB functions or VB routines (including new Form buttons) start the problem described.

    +
    0 Votes
    alec.wood

    As far as I have experienced. I am no database expert, but I have written a lot of systems here which use Access front end, some with an Access back end, some with SQL Server 2000.

    I haven't suffered the same exact problem, but I have had problems when changing the back end from one to another. My resolution was to decompile the Access front end, then compact and repair, save it,exit, then recompile and correct errors as found.

    From the command prompt, or a batch file, run

    "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "path_to_your_file\your_access_db.mdb" /decompile

    +
    0 Votes
    rayis.imayev

    Hi, yabbayogi,

    Try to "Compact and Repair the Database" option.

    There are some other things you can check, but tell me if it works. If it doesn't I can help with more options you can have.

    Rayis.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    yabbayogi

    Thanks for all of your suggestions. So, here's what happened with the suggestions:
    - Decompile Option:
    Got an immediate error of "An error
    occured while loading <Form Name>." Then
    my main switchboard form loaded (not the
    one it complained about) with an error
    "Invalid procedure call or argument". Then
    the VB Edit screen came up showing
    Access's General module, the one that has
    all the "button-click" code that it
    creates itself, but with no
    specific code indicated. I went ahead and
    Compacted and Repaired anyway, but no
    change in the problem.
    - Compact and Repair Option:
    Nope, doesn't make a difference.
    - DAO to ADO Option:
    Sounds like a possibility. Can you point
    me to an "Easy instructions for how to use
    ADO to open a database, open a table, edit
    a table's records, add to a table's
    records, update a table's records,
    MoveNext, FindNext, ...", you know, all
    those table kind of functions. I'm a self-
    taught-by-trial-and-error Access
    programmer (with an old Computer/Database
    BSc degree from back in the days of main-
    frames) so I know all the concepts but
    without any formal Access training.

    Thanks :-)

    +
    0 Votes
    rayis.imayev

    Hi, I have a very good paper-book so can?t send it to but here a good brief link you can follow for the information you?re interested http://allenbrowne.com/func-ADO.html.

    I was going to ask you, since you use SQL Server as backend, why don?t you switch your MDB Access application and transform it into ADP Access application that natively access the SQL Server via OLE-DB libraries (much easier, much stable).

    +
    0 Votes
    ineuw

    Is your current system coded with VBA and DAO or, are you using macros to manipulate data? Did you install MSSQL server, or SQL Express?

    As the first and quickest link to information, I recommend Wikipedia, where there are a lot of good (and more or less objective) articles are to be found on the related subjects.

    http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

    Microsoft MSDN has numerous articles on converting DAO to ADO and the web is swamped with various levels and qualities of information.

    I have tried ADP but found it restrictive when trying to convert VBA code. Also, it uses HTML as the data entry form. Regardless of what you choose, ADO is a requirement to connect and manipulate SQL server data. There is no other [productive!] multi user alternative in a small business LAN environment. ADO was necessary so that Access can connect to object oriented data servers Otherwise, it would have hit a dead end. The upside is that there is a lot of similarity between DAO and ADO.

    The great advantage of using MS Access as a Front End is that it has the best QBE, the best and most sophisticated form design, and very good reporting, especially when development time is taken into account.

    Your table structures will have to be modified and a date stamp column should be added to each column and filled with unique fictious dates & times for existing records. A date stamp column is a requirement with a MSAccess/MySQL configuration and it may be required by MSSQL server under certain conditions.

    Some, or all your queries will have to be modified - less so for MSSQL, more so for MySQL. The modifications depend on the table names, the current SQL statement structure, and the required SQL operations.

    What data storage system you use is immaterial. Both MSSQL and MySQL are excellent, (one is free, the other isn't) and ADO communicates equally well with both through OLEDB and ODBC.

    +
    0 Votes
    robo_dev

    My experience is that it is better to try solving by creating a brand-new database and attempt to import objects from the OLD database, rather than trying to export to a new database. It frequently seems that it ends up being usually one, maybe a couple, of objects that are corrupting the entire database.

    Although the form is stored locally, it may have a control source to some linked table. The linked table may be on a database that (was) on the network. Check the form's control source. Use the linked table manager to renew the links as some of them may have had the design altered.

    KB article on this error:
    http://support.microsoft.com/kb/304548

    "When you try to view the code for a module, try to compile a database, try to create an MDE file, or try to execute code in a Microsoft Access 2000 database, you may receive one of the following error messages:
    Error accessing file. Network connection may have been lost. "

    +
    0 Votes
    aos168b

    The error: "Error accessing file. Network connection may have been lost", is a classic sign of an unpatched Access installation. It has nothing to do with network connections. You need to install service pack 3 (SP-3) for Access 2000:

    http://support.microsoft.com/?id=304548

    Good Luck!

  • +
    0 Votes
    yabbayogi

    A final note on my question. Visual Basic functions that exist in the MS Access database from its "dBaseIII" days still work. But any *new* VB functions or VB routines (including new Form buttons) start the problem described.

    +
    0 Votes
    alec.wood

    As far as I have experienced. I am no database expert, but I have written a lot of systems here which use Access front end, some with an Access back end, some with SQL Server 2000.

    I haven't suffered the same exact problem, but I have had problems when changing the back end from one to another. My resolution was to decompile the Access front end, then compact and repair, save it,exit, then recompile and correct errors as found.

    From the command prompt, or a batch file, run

    "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "path_to_your_file\your_access_db.mdb" /decompile

    +
    0 Votes
    rayis.imayev

    Hi, yabbayogi,

    Try to "Compact and Repair the Database" option.

    There are some other things you can check, but tell me if it works. If it doesn't I can help with more options you can have.

    Rayis.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    ineuw

    I am assuming that when you previously connected to dBase III, you were using DAO, which will not work with object oriented SQL databases. When connecting to SQL databases, like SQL Express, MS SQL server, or MySQL, you need to use ADO.

    +
    0 Votes
    yabbayogi

    Thanks for all of your suggestions. So, here's what happened with the suggestions:
    - Decompile Option:
    Got an immediate error of "An error
    occured while loading <Form Name>." Then
    my main switchboard form loaded (not the
    one it complained about) with an error
    "Invalid procedure call or argument". Then
    the VB Edit screen came up showing
    Access's General module, the one that has
    all the "button-click" code that it
    creates itself, but with no
    specific code indicated. I went ahead and
    Compacted and Repaired anyway, but no
    change in the problem.
    - Compact and Repair Option:
    Nope, doesn't make a difference.
    - DAO to ADO Option:
    Sounds like a possibility. Can you point
    me to an "Easy instructions for how to use
    ADO to open a database, open a table, edit
    a table's records, add to a table's
    records, update a table's records,
    MoveNext, FindNext, ...", you know, all
    those table kind of functions. I'm a self-
    taught-by-trial-and-error Access
    programmer (with an old Computer/Database
    BSc degree from back in the days of main-
    frames) so I know all the concepts but
    without any formal Access training.

    Thanks :-)

    +
    0 Votes
    rayis.imayev

    Hi, I have a very good paper-book so can?t send it to but here a good brief link you can follow for the information you?re interested http://allenbrowne.com/func-ADO.html.

    I was going to ask you, since you use SQL Server as backend, why don?t you switch your MDB Access application and transform it into ADP Access application that natively access the SQL Server via OLE-DB libraries (much easier, much stable).

    +
    0 Votes
    ineuw

    Is your current system coded with VBA and DAO or, are you using macros to manipulate data? Did you install MSSQL server, or SQL Express?

    As the first and quickest link to information, I recommend Wikipedia, where there are a lot of good (and more or less objective) articles are to be found on the related subjects.

    http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

    Microsoft MSDN has numerous articles on converting DAO to ADO and the web is swamped with various levels and qualities of information.

    I have tried ADP but found it restrictive when trying to convert VBA code. Also, it uses HTML as the data entry form. Regardless of what you choose, ADO is a requirement to connect and manipulate SQL server data. There is no other [productive!] multi user alternative in a small business LAN environment. ADO was necessary so that Access can connect to object oriented data servers Otherwise, it would have hit a dead end. The upside is that there is a lot of similarity between DAO and ADO.

    The great advantage of using MS Access as a Front End is that it has the best QBE, the best and most sophisticated form design, and very good reporting, especially when development time is taken into account.

    Your table structures will have to be modified and a date stamp column should be added to each column and filled with unique fictious dates & times for existing records. A date stamp column is a requirement with a MSAccess/MySQL configuration and it may be required by MSSQL server under certain conditions.

    Some, or all your queries will have to be modified - less so for MSSQL, more so for MySQL. The modifications depend on the table names, the current SQL statement structure, and the required SQL operations.

    What data storage system you use is immaterial. Both MSSQL and MySQL are excellent, (one is free, the other isn't) and ADO communicates equally well with both through OLEDB and ODBC.

    +
    0 Votes
    robo_dev

    My experience is that it is better to try solving by creating a brand-new database and attempt to import objects from the OLD database, rather than trying to export to a new database. It frequently seems that it ends up being usually one, maybe a couple, of objects that are corrupting the entire database.

    Although the form is stored locally, it may have a control source to some linked table. The linked table may be on a database that (was) on the network. Check the form's control source. Use the linked table manager to renew the links as some of them may have had the design altered.

    KB article on this error:
    http://support.microsoft.com/kb/304548

    "When you try to view the code for a module, try to compile a database, try to create an MDE file, or try to execute code in a Microsoft Access 2000 database, you may receive one of the following error messages:
    Error accessing file. Network connection may have been lost. "

    +
    0 Votes
    aos168b

    The error: "Error accessing file. Network connection may have been lost", is a classic sign of an unpatched Access installation. It has nothing to do with network connections. You need to install service pack 3 (SP-3) for Access 2000:

    http://support.microsoft.com/?id=304548

    Good Luck!