Results 1 to 11 of 11
  1. #1
    jlmacfad is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    5

    Access not retaining table structure changes

    I am using the version of Access shipped with MS 365. I have been using various versions of Access for over 10 years so I'm very comfortable creating a db and creating and altering the structure of a table.

    However I have run into a strange problem. I have a database which I have been accessing and altering its data via a VB.Net app. When I try to add a new field to a table I use the Access app Design view. It appears to add the new field correctly. I close Access, reopen it and the new field is present in the table's design view. However, when I try to access the table via a SQL statement in my VB app it fails. When I re-open the database using the Access app the new field has disappeared.

    I've never run into a situation, with previous Access versions, where I've been unable to alter a table's structure. Any suggestions would be appreciated.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you show the SQL statement (and maybe the entire procedure) in the VB app. Usually after you change a table in the Access back-end you would need to refresh the link in the front-end application in order to see the changes. But it almost looks like your VB app is resetting the table structure. Is it a commercial app? I would look at what codes the VB app is running when it is opening, you might see some drop/create DDL statements in there.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    jlmacfad is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    5
    Vlad:
    The SQL statement is a simple Select * from...... syntax.
    As far as your other comments. There is no explicit link between the DB and the VB app. The app connects to the DB via a
    " New OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0 ; Data Source=" & DataBase_File_Path)" syntax. The app doesn't issue
    any SQL statements related to the db structure ( ie Create Table, etc)
    I'm completely baffled how the structure can change without anyone accessing the db via the Access app.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It shouldn't so you have two probably causes:

    A. There is something that resets your Access file to an earlier version after you edited the table and added the field: I have seen this happening when the Access file is placed on "online" folders such as OneDrive or Google drive and also on some LANs running file synchronization.

    B. The change you make in the Access interface doesn't actually get saved; ensure you open the database in exclusive mode when you add the field and there are no other connections to the file.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    jlmacfad is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    5
    Thanks for the suggestions.
    Regarding the location of the DB, it is not on OneDrive or Google folder. Standalone workstation.

    Regarding the second point, I have made the change, saved the db, closed the MS Access app and then reopened it. The changes were still there. Only after being accessed by the VB app has the structure reverted back to its former structure.

    How do I ensure the db is opened in exclusive mode??

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is the file on a network drive or the local C drive of the workstation? If network try to move locally and update the connection string in the VB app and try. To open the file exclusively first open your Access app then go to File/Open, select your file and click the drop-down in the Open button on the file dialog and select exclusive.

    Are you sure you checked all the code inside the VB app to look for DDL SQL statements? How do you open the VB app? Does it have a "launcher"? If yes maybe that is your culprit.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Long shot but also check that you do not have Offline files enabled on the folder hosting the Access file:
    https://www.tenforums.com/tutorials/...s-windows.html

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    jlmacfad is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    5
    Is the file on a network drive or the local C drive - C drive
    Are you sure you checked all the code inside the VB app to look for DDL SQL statements? - I wrote the app and know that I don't use DDL statements.
    Am running out ideas......may have to go to SQL Server

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    One last thing to try:
    add the field in Access like you normally do, save the table then save a copy of the table in that same Access file. Close Access and reopen, if both tables are still there then close and open VB app. Finally check for the presence of the table copy after you exit VB. If not there than something on your workstation is overwirting the entire Access file.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    jlmacfad is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    5
    Tried your suggestion and both tables (Original with field change and copy of changed table) stayed in db. Closed Access and reopened.....both tables still present.
    Ran VB app via its exe. Anything remained in place.
    Now it gets interesting....opened the VB app using Visual Studio 2019. Rebuild the app and ran it......copy table now gone and new field in original table also missing. Appears VS 2019 is the culprit
    Am going to try to rebuild EXE via VS 2019 but then run the app from outside the VS environment

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Looks like you got your answer....
    Good luck with your project!
    Cheers,

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-09-2019, 05:34 AM
  2. Replies: 2
    Last Post: 09-29-2019, 07:26 PM
  3. Replies: 3
    Last Post: 10-12-2016, 06:57 AM
  4. Replies: 2
    Last Post: 06-25-2014, 10:24 PM
  5. Help with Table Structure
    By medtech2 in forum Database Design
    Replies: 5
    Last Post: 10-14-2011, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums