Question Records created on one PC are not visible on the other

[kvikhrov]

Greetings, everybody!

I have developed an application in VB.NET that works with MS Access database. I installed this software on one PC (Windows 7), which is considered as the server, since it is hosting the database sharing it on the local network. The empty database file is installed together with the other files of my deployment project package, which is a part of my VB.NET solution. The software works fine on this machine.

Then, I installed the software on a different workstation (Windows XP). The connection to the db is established without troubles, except for the unpleasant detail that no records created by the server's application are visible.

Besides, when I open the database file and check any table, no entry appear either (neither openning the file on the workstation, nor on the server itself). The structure of the tables is OK, though.

In other words, the data registered by the server's software can only be viewed through the server's software. And the data inserted by the other workstation or openning the db file directly can be viewed anywhere, except through the server's software.

I have revised the Windows 7 permissions for the db folder and file to accept remote connections. So did I for the db MS Access properties. The ConnectionString I use for the software to work with the db had always worked fine in other client-server applications with MS Access.

I suspect there's a sort of a data protection mechanism that comes either with Windows 7 or with MS Access 2007. Or I should definitely deploy the db file separately from the other software dependecy files (thing I have not tried yet).

Any advices on this case will be appreciated a lot, because my patience is beginning to run out.

 

[Menthos]

Hi,

You mention that you're deploying an empty database with the project...do your connection strings point to the deployed database? and if not, what's the reason to include this database with the project?

It sounds as though your app is pointing to the local, deployed database, rather than your 'server' version. If your connection strings are set to correctly point to the correct machine/db, then test connectivity from the client boxes - a quick odbc connection should tell you if you're able to connect to the 'server'

 

[kvikhrov]

The only goal of compiling the empty db file into the installation file, is to have every resource the system might need in one deployment package.

When the system runs for the first time the user must indicate the db to use. As I am the one who runs the application first, I make sure to select the shared db from the terminals (\\Server\FolderName\DB.mdb) or the local db on the server (C:\Program files\FolderName\DB.mdb). These are the values of the ConnectionStrings on each machine respectively.

The point is that the changes made to the db from the terminal are visible only on the terminal. On the other hand, the changes made on the server are only visible through the server's application. Definitely, the symptoms are as if each PC were working with a different db. However, to discard this, I even used Windows search tool, looking for any *.mdb file on the server. And the only MS Access file found is the installed one. So, there seems to be no way for another db to exist, unless it is kind of hidden or protected from the other users to see its data, which is only accessible for the software installed along with it (thing I consider little believable).

There is more, when the db is not found the system asks the user to select one. However, when I moved the db file to a different directory, the installation dialog showed up instead (since the db is a part of the deployment package I guess) not giving me a chance to select the db in its new path. So, maybe it has something to do with this issue.

 

[kvikhrov]

Problem solved

Hello, Menthos.

Thanks a lot for your kind intention to help me out. I did double-check my ConnectionStrings on each machine, but "unfortunately" everything was OK (pointing to the same shared DB on the server).

Therefore, for those who may have a similar case, here is what I did to work it out:

- As I had told previously, the data registered via server's application could be visible through the server's application only. Not even openning the DB file with MS Access to view it directly on the same machine. Even the size of the DB file remained the same as it was when I just installed the software.

- The application is designed to show the OpenFile dialog to select a DB. When it runs for the first time or when the referenced DB is not found. I discovered that the size of the DB file did appear to be greater viewing it through this dialog window.

- Besides, the Windows 7 on the server is the spanish version. So, the physical directory (in the Windows Explorer) where the software was installed appeared as C:\Archivos de programa\Application. However, checking the ConnectionString of the server's application, the referenced path turned out to be C:\Program files\Application. Then, I got even more confused, because somehow the connection did get established.

- So, I decided to avoid messing up with C:\Program files directory. Placed an empty DB in a different partition and folder (D:\DB) which works perfectly with any machine.

- After that, I copied all the existing data from the troublesome DB to the empty one. To do that, I temporarily added several queries in the source code of my software, since it was the only mean by which the info could be viewed. That's because all the existing data has been registered by the server's application so far. Having the connection object point to the troublesome DB, I used the following sort of quiery for each table:

INSERT INTO [MS Access;DATABASE=D\DB\DB.mdb;PWD=password].[TableName]
SELECT * FROM TableName

- Finally, I shared the new DB on the network, redirect each machine's application to the new DB.

I really never found out what the core of my issue was: C:\Program files directory Windows 7 protection mechanism, deploying the DB file as a part of Visual Studio installation package or the path name differences I described above for a different Windows language version in a ConnectionString.

I just found a work around and really hope these notes will help other guys who might get into similar misleading and ugly situation.