Ports used with VB6 database access.

[tedsmith]

Maybe someone can enlighten me as to what network ports are used when using a vb6 recordset to query a database on another computer?

I want to be able to use a vb6 recordset to query or write to a database in another computer on a different gateway. Also see the data in a datagrid.

This works fine as long as file access is allowed in the Cisco switches (tested by entering \\IPaddress in Windows explorer and being able to see the mdb file there).

The network firewall and Cisco switches are set up (by others more knowledgeable than me) so the two computers can communicate only via their IP addresses in only a Port range 1000 to 1010 to the remote computer, any port the other way. Firewall is one way.

This means that normal database file and graphics file access is blocked (which is what they want to keep for security purposes)

Is there any way I can use another port that is not used for file access so my vb6 recordset and datagrid can open the database file? (without enabling Windows Explorer)

The firewall is one way. At the other end, it can see everything on the first computer OK because it is in a secure room so it can send back files requested on the TCPIP port.

I tried having a copy of the recordset in the other end and relaying results via commands over TCPIP but this is far too slow and cludgy.

 

[JoeAtWork]

Errr, what???

Connections are made with ADO Connection objects, not recordsets. What kind of database are you trying to connect to: SQL Server, Oracle, MySQL, etc.?

 

[dilettante]

Seems clear the question is about Jet MDBs.

No "ports" are used. Jet is an embedded "file based" DBMS.

When you open a Connection in ADO (even if via a Recordset's ActiveConnection default object created by Recordset.Open with a connection string), ADO loads and calls the Jet OLEDB Provider (if you haven't done something sloppy and slow like invoking the old ODBC Driver instead via the ODBC shim Provider).

But in either case, this causes the Jet engine to be loaded and run in-process. The Jet engine opens the MDB file (and its related MDW file if you are doing things correctly, but few seem to anymore). At that point an LDB lockfile is created (unless opening read-only or exclusive), to coordinate updating among multiple "connections."


So you need Microsoft Networking/LAN Manager access to the file share where these files are stored. MS Networking (also known as File and Print Sharing though there is much more to it) requires multiple ports to be open for it to operate. It was never designed to be used across the Internet and is not firewall-friendly or Internet-safe. Even if you allow it, the likelihood of database corruption becomes quite high. There is no way you will get it working with a small handful of ports allocated at random values like 1000-1010.

I guess those are the ports you were talking about: those used for MS Networking.


The most obvious route is to move to another DBMS that offers the option of client/server access over a single TCP port, preferably configurable to ad-hoc values in your acceptable range.

Barring that, you need to write a middle tier application that sits near the database (or on the same box preferably), and then rewrite your application to use a custom protocol to talk to the middle tier over TCP 1000-1010. This means a logic rewrite as well as a rewrite eliminating ADO in most cases, because the database access will be "semi-connected access" much like a Web Service.

There is an old technology called Remote Data Service (RDS) that is still supported. This would let you keep using ADO in your application but will most likely require some logic rewrite effort. It is also semi-connected in its interactions. For WAN use it must be tied to IIS at the database end and uses HTTP as its transport, but setting up IIS to work with it these days is tricky - it is turned off by default now and it takes some effort to turn it back on. You'd also have to relocate an IIS virtual server ("Web site") for RDS to use one of your available ports (1000, etc.). Then there is additional RDS configuration to be done on the server... well it has become a lost art and is subject to layers of security that were added over the years.

RDS can act as a generic middle tier, saving you the effort of writing your own and inventing protocols at least. RDS was created for this very sort of scenario, and under the covers uses one of the earliest Web Services.

This "rewrite for semi-connected access" can be very tiny or very large depending on how your application uses the database now. Since you mention DataGrids though... well that sounds bad, as in lots of rewriting.


Even if you move to something like SQL Server though it sounds like your application design is such that performance will really stink. Programs are not normally supposed to suck entire tables or large JOINs from databases into Recordsets, and never over a WAN.

 

[tedsmith]

Sounds diabolical! I don't want to reinvent the wheel!

The Database is Jet about 100,000 records of 20 columns (up to 50mb table)
It was created by CREATE and ALTER TABLE type Execute commands and can be viewed with MSAccess2000 or later.

It's not on the web but on an exclusive government private 1g fiber optic network with no connection to the web.
I only need to know a few records at a time, it doesn't transfer the whole table when you look at it.
I can execute a code query from a workstation either using OpenRecordset or OpenDynaset or a normal SQL Execute"...." to very quickly so there is no speed problem.
The same file is also accessed and updated by a local app running in the 'server' using the same techniques and there has never been a single database problem or conflict in 2 years (running 24/7)

My problem is it has been running OK with file access allowed but the user now want to have extra workstations running in some not so secure locations with file access restricted (TCPIP is allowed)

Is there any other way around this?

I suppose I could have all the queries done at the server and just feed questions and replies in text via TCPIP on one port but this means a awkward rewrite. It would probably be just as fast as the network runs faster than the hard disk in the work station!

 

[gmmastros]

How many tables are in the database? How many queries are access the database?

I ask because if the numbers of tables is small, and the number of queries is small, it would be a relatively simple matter to change out the database engine to something that lends itself to TCP connections. The more tables and queries, the larger the effort.

I have a ton of experience with SQL Server. With SQL Server, you really only need 1 open port (2 is better so you can use the SQL Browser, but it's not necessary).

There are some minor differences in the SQL Language between Access and SQL Server. For the most part, the differences are not terribly difficult to get used to. SQL Server allows for more functionality and has a lot of advanced features not found in Access. However, as a straight up replacement for Access (especially with a small table with only 100,000 rows), the level of effort required is smaller than you may think.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

Sounds interesting.
There is only 1 large table and about 5 other tiny tables that contain fixed data, some with only 1 record, others with say 20 records. No joined queries are used, rather I get data from the fixed table by executing a code query that might return 100 records from the large table.
Then I get different fixed data and repeat the original query with new parameters and so on (I find each simple query is actually faster than having a complex query and I want each query separated in time anyway by 1 second so I am repeating every query once every 20 seconds with different criteria.

I am a bit hazy as to the exact structure a SQL Server setup would have to have.

I need a vb6 app at the server and the workstation to interface with equipment at the server end and humans at the workstation end.

Currently I use a Jet database, This every week gets update information from a database table supplied in MSaccess 97 from so I use an update query (in vb6 code) to my master Jet table

If I use a SQG Server table instead, can I still use vb6 in the same way or do I have to have a special SQL server app running?

How do you set a particular port to be used?

 

[gmmastros]

VB6 can connect to a SQL Server database in a very similar way as the connection to an Access database. Obviously the connection string must be different, but that's about it.

In some cases the queries will need to change. This is because the SQL for each database is different in several subtle ways. For example, the date delimiter in Access is the # symbol. In SQL Server, it's the single-quote '.

Since you are dealing with a very small number of tables and simple queries, the process of converting your app ought to be simple.

The Database is Jet about 100,000 records of 20 columns (up to 50mb table)

You'll need to stop thinking of 100,000 rows as a large table. For SQL Server, this is almost nothing.

As for the connection string, you should check here:

http://connectionstrings.com/sql-server-2005

It sounds like the client computers will not be on the same domain as the server, so it will probably be easier to use SQL Authentication. Therefore, your connection string will probably look something like this:

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Note that the Server allows for certain variations. Usually this is the computer name of the SQL Server machine. You can use IP address and port instead. For example:


Provider=SQLNCLI;Server=192.168.1.10[!],1001[/!];Database=myDataBase;Uid=myUsername; Pwd=myPassword;

The red part would be the port number.

I suspect you will be using SQL Express, which is the free version of SQL Server. When you install SQL Express, it will randomly pick a port number (by default). There is a SQL Server configuration utility that allows you to enter any valid port number.

Your process for adding data to the database on a weekly basis will need to change, but adding data is a super common thing to do. There's probably a dozen ways to do that.

I would encourage you to download "SQL Server 2008 R2 Express" from Microsoft. Install it on your local computer, and play around with it for a little bit. You're likely to have questions along the way, but you know where to find us.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dilettante]

Yes, you need some system dedicated to run a SQL Server instance. This can't just be a file server, but a machine set up to run services. While in theory you could dump it onto one of your client systems, but generally this isn't very practical.

It also means additional administrative issues. SQL Server isn't meant to run without attention.

SQL Server issues should be directed to the appropriate forums though, not here in this one.

One factor to consider is discussed in Is there a way to stop SQL Express 2008 from Idling? This is an issue for user-driven applications that are not in continuous use, where your walk up users may see strange lockups and lag when they try to do something when the database has gone to sleep after 20 minutes.

You can get around this by forking out for a "real" edition of SQL Server, one meant for production use.


But yes, you probably do want some kind of client/server DBMS.

 

[strongm]

> it doesn't transfer the whole table when you look at it

Er ... yes it does. Jet has no remote capability at all. When you acccess a table Jet has to open the entire table locally, even if your query only returns a few records. Dilettante has already tried to explain this.

 

[gmmastros]

[tt]Yes, you need some system dedicated to run a SQL Server instance. This can't just be a file server, but a machine set up to run services. While in theory you could dump it onto one of your client systems, but generally this isn't very practical.[/tt]

With 50 mb of data, any ole computer would suffice. Even an old computer.

[tt]It also means additional administrative issues. SQL Server isn't meant to run without attention.[/tt]

With one real table and several lookup tables, the administrative overhead is going to be minimal.

[tt]SQL Server issues should be directed to the appropriate forums though, not here in this one.[/tt]

I think this conversation is appropriate because Ted's asking about RDMS's that utilize TCP/IP ports.

[tt]One factor to consider is discussed in Is there a way to stop SQL Express 2008 from Idling? This is an issue for user-driven applications that are not in continuous use, where your walk up users may see strange lockups and lag when they try to do something when the database has gone to sleep after 20 minutes.[/tt]

Ted is repeating the same query every 20 seconds so the idle time issue won't be a problem. Of course, the database should be configured for AutoClose = False.

[tt]You can get around this by forking out for a "real" edition of SQL Server, one meant for production use.[/tt]

This is true, but only if the idle time issue becomes a problem, otherwise there will be no difference in performance, especially with a small database and simple queries.

[tt]But yes, you probably do want some kind of client/server DBMS. [/tt]

I agree. There are several to choose from. I'm a big fan of Microsoft SQL Server which is why I suggested it. Certainly there are plenty of other choices that would work in this situation.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom