VB.net Windows Form Application with SQLite DB not finding Database

[DMoody007]

I was asked to modify a Windows Form Application developed years ago by a previous employee. It is written in VB.net and is using ADO connection to a SQLite Database. The application was first developed on a Windows 7 system and was published as an X86 app. It was last modified on a Windows 10 system still as an x86 app. I am on a Windows 11 system and tried to publish the application as a X64 and it won't work. I don't have a version of the original System.Data.SQLite.dll made for x86 and don't seem to be able to find it anywhere to put back in the project.

On to the issue, the application is deployed to over 150 users. This is supposed to be an upgrade. When the app installed as an X86 app, it installed the program in c:\program files (x86) and it put the SQLite database in c:\users\{username}\appdata\local\virtualstore\program files (x86)\{publisher name}\{app name}\_Data\{Database name}

When I install the X64 upgrade, the application installs as an upgrade but when we open the application is says Connection Error: Unable to open the database file

It doesn't find the existing database in the relative path and I can not determine if it installed a new DB file anyplace different. I tried searching for the DB name, and I only see the one in the relative path and one in my development folder of the application.

Been reading some articles that make me believe the application was not written using best practices. Here is an example of the code for the ado connection:

        
        sqlite_conn = New SQLiteConnection("Data Source=.\_Data\myData.db;Version=3;")
        sqlite_conn.Open()
        sqlite_cmd = sqlite_conn.CreateCommand()

All the connections appear this way and I am pretty sure the .\ is the root of all evil. X86 appears to like it and x64 wants to do it another way.

I am probably not going to be able to find System.Data.SQLite.dll 1.0.113.0 in x86 so I am trying to determine the best way to move forward. I have the 1.0.113.0 in X64 now.

Since the app exists on other computers, I need the upgraded version to reference the database that exists on that computer. Starting from scratch is not realistic. So, thinking I have two possible solutions but I am not seeing anything that can help me go either direction.
1) Change connection string to the path of the x86 database by using something like %localappdata% to get past the userid part of the path. App would probably not debug in code because of the specific path but hoping it would work when installed.
2) Modify the deployment to specify the location the database to be something like ProgramData\{Publisher}\{AppName}. Then maybe write some code to move the old database to the new location and allow overwrite. Not 100% sure overwriting the DB with an existing DB will work or possibly cause a corruption issue.

Open to reference articles and advice on how to proceed. Been 3 hours deep in Google and AI and not feeling like I am progressing so any direction would be appreciated.

Thx,
Douglas

[Zvoni]

Usual suspects:
1) sqlite-driver 32-bit vs. 64-bit
2) "VirtualStore" --> usually a hint, that "originally" (Windows 7?) the Database WAS actually in a SubFolder directly in "C:\Program Files (x86)\MyProgram\_Data", which requires Write-Access (Admin?)
There is a "Virtualisation"-mechanism in Windows to prevent Apps trying to write to folders something like "C:\Program Files (x86)\MyProgram\_Data" to crash

As for copying/moving the db-file itself: There is no Problem by just moving it. the db-file itself is "Bit-agnostic". It doesn't care if it was created using the 32- or 64-bit driver

Be careful with "ProgramData" --> This one usually also has Write-Access-Barriers, nevermind any user logged on that computer would have the same data

Bottom Line: Remove/change all code which "originally" refers to "C:\Program Files (x86)\MyProgram\_Data" and use "%APPDATA%"
Should be something like "Application.StartupPath" or similiar
This also includes any References to RELATIVE paths, since that one always try to resolve the Application-Path first

EDIT: This thread belongs IMO to the VB.NET-Subforum, not Database (even though it's about sqlite). It has to do with locating files