Connecting to MDB on another computer

[tedsmith]

I had a problem (so I thought) of using a VB6 opendatabase connecting to a MDB on another computer that is externally firewalled so I can't see folders (although I can ping it) although is has one port 1000 enabled so I can winsock to it OK

However only when I open and connect the winsocks, I find I can also open and read a database on it even though I can't see the MDB file in Windows explorer!

Can anyone explain this?

Is it feasible to create a pseudo Windows Explorer that works via winsock and what steps would be necessary?

 

[dilettante]

There is nothing we can really say without knowing a lot more about the network and your environment.

What does the remote share look like in your connection string? A mapped drive letter?

What makes you certain that opening some TCP connection made a difference? Have you tried opening the database or a file on the share without connecting? Perhaps something is just preventing you from browsing folders via Explorer.

 

[strongm]

I'm beginning to suspect that the much vaunted security on your government system isn't quite as leakproof as they might be hoping ...

 

[tedsmith]

Database connection definitely only works when TCP connection is connected. There are only 10 ports available to the server 1001 to 1010 and they are all used by computers permanently connected, this workstation being one of them.

No drive letters mapped at all.

To see another computer on Windows explorer I simply type "\\Ipaddress"

I can see the workstation from the server but not in the other direction. This is a fiber optic network over many miles with a series of Cisco switches between, set to only allow certain ports and addresses

Fixed IP, Subnet & Gateway values on both, Subnets and Gateways are different this is apparently handled by the switches.

All computers have all permissions set (XP64bit OS)

I use 2 "usual' methods of connection
Example of showing in a datagrid

 CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BuswayRMS\LEDMessages.mdb"
            RS.CursorLocation = adUseClient
            Criteria = "Select * FROM LEDmessageSchedule WHERE SID =" & StationClient & " ORDER BY SNo"
            RS.Open Criteria, CN, adOpenStatic, adLockOptimistic  ' Opening the recordset"
            Set DataGridSchedule.DataSource = RS '

Example of a recordset

Set MyDb = OpenDatabase("C:\BuswayRMS\Ledmessages.mdb")
        Set MyRec = MyDb.OpenRecordset("SELECT * FROM LEDMessagecontent WHERE MNo = " & MNo, dbOpenSnapshot)

 

[strongm]

Try running [tt]netstat -n[/tt] in a command prompt once you have the database open. Should tell you what ports are actually in use.

 

[strongm]

(mind you, your code examples here seem to indicate a local database)

 

[dilettante]

Who on Earth uses XP64? This makes no sense because of the many things never perfected until Vista and the few systems that supported x64 until the past 5 years. Sounds as if somebody bought a new machine with something later (Vista, Windows 7) preinstalled then blindly did a "downgrade rights" install of XP64 because they are utterly clueless.

Why are you mixing DAO and ADO? You really, really only want to use one database connection to each database in a given program anyway.

It is remotely possible this network is so poorly configured that some subset of SMB/CIFS ports are open allowing authentication and file access but no browsing. Seems unlikely but this whole scenario sounds like The Three Stooges created it. Since the server system can browse clients but not vice versa we already know things are a mess.

The problem might be fixed by simply opening UDP 445 through from clients to server (TCP 445 seems to already be open). But that assumes direct hosted file sharing, and you may be operating over some broken subset of the NetBT ports instead.

I don't buy this "opening a TCP connection in the port range 1001 to 1010 permits file sharing to work" story though. While anything is possible that one stretches credulity. Again: Have you actually tried a program that only opens the database connection or opens some file on the file server?

 

[tedsmith]

The code is a representation of the method I use.

The source is actually a string that changes to
\\C\BuswayRMS\LEDMessages.mdb
when I want to connect.

In will investigate your other suggestions further.

I use both methods because it is the best way to do it!

First method is very convenient and reliable for filling a grid for monitoring & reading (I hate ADODCs)
You can quickly change the datagrid.Recordsource (I have 34 different databases to check, all being the same format)

Second method is much faster and convenient for making changes
I open use, and and shut it in the same operation.

I always like to never keep a connection open for longer than necessary. It's not like I have thousands of connections.

 

[CP60]

Using ADODB and DAO on the same database is not a problem if both are opened with static/snapshot cursors - and no writes (therefore, no locks).

But if you are going to write to the db, then use either one or the other to do the writing, but never ever both!
You will still have problems with data refreshing immediately through the other connection, as the connections use indepenent writes cache.

You can use DAO with the DBGrid and not have to use the DC for this, except as an object to pass your recordset to, and with absolutely no side affects. And you can just as quickly change the grid's Recordsource.

However, if you have datagrids already set up to use ADO and in your case you are only opening it with no writes, then I would leave it as such.
It may get slow if you are loading alot of records in which case you might consider opening Async so that while the data is loading, the user immediately sees the data already loaded.

 

[strongm]

An additional question, Ted. When you kill your winsock connection does the ability to open the mdb persist?

 

[tedsmith]

Hmm - never tried DAO on a datagrid using only code.
Have you a short example of the code required?
I've never seen one in help.

This is a public info system showing real time departures at bus stops.
The only time a datagrid is ever used is when someone wants to check or change the timetable maybe a few times a day.

Otherwise it executes about 50 DAO recordssets every second feeding the latest info out to bus stops and recording where each bus is. Each one only takes less than 1ms so I never have any problems with conflict.

I did some tests a while ago and found that DAO was generally 7 times faster than ADO.

 

[dilettante]

I did some tests a while ago and found that DAO was generally 7 times faster than ADO.

Ok, now it's starting to get deep in here! ;-)

 

[CP60]

>Have you a short example of the code required?

As mentioned: "not have to use the DC for this, except as an object to pass your recordset to"

You basically do it the same way with the ADODB DataGrid, except you assign the recordset to the DC which was set the the Grid's datasource.
The recordset will be passed thru to the data grid's datasource.
I have used this method a long time with concurrent users and never ever had any problems as ther are with the typical DC problems, simple because the DC isn't handling the connection but your in-code connection object is doing the work.

So, add a DbGrid and a DC and set in the properties window the grid's datasource to the DC: But do not change anything in the DC - no recordset info and no connection info!!!

Add a DAO recordset object variable at module level, and open it (just as you are now doing with the ADODB DataGrid's recordset)

In code assign the DC's Recordset property to the recordset object variable.

2. DAO will be faster simply because there isn't that extra layer used between your code and the dbms. The dbms itself - Jet 4 -is basically the same. It is a matter of getting data to and from it, where DAO directly interfaces with JET, and ADODB doesn't.

I am not sure if "7 times" is always a fair representation - it depends on "for what and how". I can return the first thousand records of a 100,000 record-recordset for display in a grid 10, 20, 50 times faster with ADO than DAO, simply because under ADO you can use Async fetching.

As long as DAO, (and any DAO-bound controls used with it - though these are not needed) works for your needs on all future operating systems, then there is no reason to change to ADO...until it doesn't.

If one day DAO ceases to work on an OS (or your requirements change: More data, more concurrent users, different dbms, etc), then you will be in need of a fast change of code in order to adjust your application to using ADODB.