Can I query a database table in another computer using only winsock?

[tedsmith]

I want to be able to read and write to a mdb table in another computer that is beyond a substantial firewall.

Currently I can only connect to this computer and exchange data between two special applications written by me on a fixed IP address, subnet, gateway and a small fixed range of ports only used by me.

This is a special data monitoring thing in our Government's central computer system and direct file access is barred for security reasons so I can't connect by remote in the usual way to a .mdb in the other computer.
I want to add database capability to this system to locally store data and retrieve it later.

Other than communicating via Winsocks to a routine in the firewalled computer that locally reads and writes to the table, then relays the data back, is there any other smarter way to access data on the table via the same ports as I already use?

I visualised some sort of mirror setup where a blank table in my computer mirrored the data in the other computer?

 

[gmmastros]

It's a shame you're not using Microsoft SQL Server. To connect to a SQL Server instance, all you need is an IP Address and a tcp port. In this situation, you wouldn't need to concern yourself with winsock. So, theoretically, you could install SQL Server on the remote computer, and then configure a linked server to your mdb file or even import the data in to SQL Server and forget about the mdb file. If you decide to go this route, please be aware that the SQL involved with SQL Server (T-SQL language) is different than the SQL you use for an Access database. For example, in Access, the date delimiter is #, but in SQL Server, you use a single-quote (').

This may not be that helpful for you, but it is something to consider.

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

 

[tedsmith]

Thanks, sounds good.
What exactly do I need to implement SQL Server?
Do I have to purchase an expensive Microsoft application or development system just to perform a simple task like this?

What needs to be loaded on either computer and can I incorporate it in the existing vb6 apps (like an activeX) rather than have two apps running at the same time in each computer?

There is only one port allocated so the SQL server would have to share it with the existing transfer of data so being in one app would make this easier to control.

 

[strongm]

>For example, in Access, the date delimiter is #, but in SQL Server, you use a single-quote (').

Since Access 2003 single-quotes have been accepted as date delimiters

>What exactly do I need to implement SQL Server?

SQL Server Express is a free download from Microsoft

>SQL server would have to share it

That might be a problem

 

[gmmastros]

What needs to be loaded on either computer and can I incorporate it in the existing vb6 apps (like an activeX) rather than have two apps running at the same time in each computer?

SQL Server would be installed on the remote computer. You would probably want to install SQL Server Native client (free download from Microsoft) on each computer as part of the install application. This is very small and doesn't take much resources. In the connection string you use, you would specify that you want to use the SQL Server native client (instead of jet). Your application wouldn't need to run on the server, and SQL Server wouldn't need to run on your client app computers.

There is only one port allocated so the SQL server would have to share it with the existing transfer of data so being in one app would make this easier to control.

This may be a deal breaker here. As far as I know, you cannot share TCP ports among applications that are running simultaneously. SQL Server runs as a service so it is always running. It would grab the port when the machine starts up and not let go (unless you stopped the SQL Server service).

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

 

[tedsmith]

I would be happy "time sharing" the use of winsock and SQL, they don't really have to be running at exactly the same time.
This poses the program of how to start the SQL server and stop it from within the vb6 code of the app in the server. I could easily do this by remote control by the winsock once the start and stop methods were known.

The winsock would be in use 95% of the time and the SQL server only needs to be open for a few seconds when needed.

I remember some time ago a thread on stopping services from within vb6 but can't remember which one it was. Is it possible to start one as well?

 

[gmmastros]

There is a command line method to stop and start services.

NET STOP MSSQLSERVER

NET START MSSQLSERVER

I don't really recommend this method though. SQL Server will cache your data in memory to speed up the performance. By stopping and starting the service, you will lose the memory cache and likely cause performance problems.

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

 

[strongm]

>a small fixed range of ports

Are all the ports in use? Or can you get one more assigned to you? You just need one port for remote access to SQL Server.

 

[tedsmith]

Unfortunately I only have one allocated fixed port per station.

Could I somehow just remotely disconnect the SQL server without actually closing it?
or Flush the cache so I don't loose any data?

 

[crystalkiwibruce]

Have you considered creating a web service on the machine and accessing the table that way. Just uses Port 80 and XML files on the computer-computer boundry.

A web service for one table is easy to write. Just returns a ADODB record set.

This assumes you have some control over the remote machine with the MDB

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[tedsmith]

Unfortunately my customer has port 80 locked out in the firewall.
They are paranoid about people hacking into their system via from the internet.

It sounds pretty primitive but I worked out a way of every time I query the database from remote I retrieve the contents of the rows needed to a temporary "mirror" database in the remote station (using propertybag with the propertybag field names the same as the database field names so any changes will be reflected)
I just have a 'Save' button to relay these altered or new contents back to replace the original database.
It hardly takes half a second to save with quite a large database and I can time share the same port.

I suppose I have created my own sort of SQL server!