I need some help. I want to open one Access DB that is on my network, but the only way I know is mapping the server drive as a logical unit on the client machine. This method uses NETBEUI protocol, and I need to open the Database over TCP/IP protocol. It means, I dont want a logical mapped drive, instead I need to open the database through out one IP address (255.255.234.234, by example). Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to open Access DB over TCPIP 4
- Thread starter mxblue
- Start date
-
4
- #2
Check out They have a free socket control and it rocks! All the socket stuff I have built I've used this control and it works great.
Once you get the control add it to your project. Actually you have to write two projects, one being the client and one being the server. The server part will be a listener. The demo they include with it shows how to build a small client server Chat application. Just build on this. You will have to pick a service port for it also.
The listener will have to run all the time on the machine that has the Access database. If you take it down, the clients won't be able to connect to it.
Basically what you have to do is build a DLL for the client side that looks like ADO as far as the properties and the methods. Then for each property and method you have to encode each funtion/property to send a request to the server/listener. The server must get the request, interpret it, perform the function and send some information back.
For example, your DLL (Client) side will have properties like
.EOF
.BOF
.RecordCount
...and will have methods like:
.MoveFirst
.MoveNext
.MoveLast
.Open
.Close
...etc.
You can choose to emulate as much of ADO as you want to. If all you want to do is open, read records and close, then implement the properties/methods above. If you want to be able to add, update and delete, then you'll have to add and implement these methods as well.
In the past I have typically used a request code for each function I want to implement. For example
.Open = 1
.Close = 2
.Execute = 3
.BOF = 4
.EOF = 5
...ect.
When the client application uses invokes one of these properties or methods, an ActionCode (as listed above) is sent to the server, along with any parameters that also need to be sent. Your request needs to be serialized and sent down the socket to the server. For example, lets look at the .Open Method.
Typically a recordset is opened in ADO like this:
Set rsCustomer = New ADODB.RecordSet
rsCustomer.Open <SQL Statement>, <Active Connection>
Your class on the client end would have to have two parameters... Your SQL statement and the active connection. So your Open request would look like this:
ActionID=1
strSQL="SELECT * FROM Customers"
When you serialize it, it might look like this:
1;SELECT * FROM Customers
The server side will also have to know that a 1 means Open a recordset. On the client machine, you just program ADO as you always have. Open a recordset and run the SQL statement against it. ADO has a .Save function where you can save the recordset to a file. You can do this and send the entire thing back to the client, or you can leave it open and when the client does a .MoveNext do a .MoveNext on the server and return the record that way. There are serval posabilites on how to implent this functionality.
I would suggest some preliminary architecture and a mapping of what properties/methods you would like to have available and start there. Then start assigning ActionID's/numbers to each property and method. The value doesn't matter as long as the Client and server know what each ActionID means.
Then start implementing base functionality like establishing a connection to the server over a socket. Then close the connection over the socket. Then start implenting opening and closing recordsets, then work on the navigation, BOF/EOF and finally .Add, .Update and .Delete if you choose to have that functionality.
Your connection object will be slightly different from ADO's connection object, since you have to provide an IP address and Service port. Encapsulate this in your code so the programmer implementing your objects doesn't have to how it works behind the scene.
Your connection object might be used like this on the client.
Dim objConn As TCPConnection
Set objConn = New TCPConnection
objConn.IPAddress = "192.168.0.1"
objConn.ServicePort = 921 'Or whatever port you want.
objConn.UserID = "Jim"
objConn.Password = "123main"
objConn.Open
When you open your recordset like this:
rsCustomer.Open strSQL, objConn
You can have the Recordset object retrieve the properties from the Connection object to figure out what database to hit. You can get all the IP and Service port information from the connection object to open your recordset.
Of course things like row level locking and such will be a pain in the you know what...
I've never written this particular functionality, but have thought about it several times. I did write one that was very similar to it where it passed XML back and fourth across the network and it used ActionID's and it worked great! It was really quick too and supported multiple clients connected to the server at the same time.
Good Luck!
-Steve Snaggs
tribesaddict@swbell.net
To define recursion, we must first define recursion.
Once you get the control add it to your project. Actually you have to write two projects, one being the client and one being the server. The server part will be a listener. The demo they include with it shows how to build a small client server Chat application. Just build on this. You will have to pick a service port for it also.
The listener will have to run all the time on the machine that has the Access database. If you take it down, the clients won't be able to connect to it.
Basically what you have to do is build a DLL for the client side that looks like ADO as far as the properties and the methods. Then for each property and method you have to encode each funtion/property to send a request to the server/listener. The server must get the request, interpret it, perform the function and send some information back.
For example, your DLL (Client) side will have properties like
.EOF
.BOF
.RecordCount
...and will have methods like:
.MoveFirst
.MoveNext
.MoveLast
.Open
.Close
...etc.
You can choose to emulate as much of ADO as you want to. If all you want to do is open, read records and close, then implement the properties/methods above. If you want to be able to add, update and delete, then you'll have to add and implement these methods as well.
In the past I have typically used a request code for each function I want to implement. For example
.Open = 1
.Close = 2
.Execute = 3
.BOF = 4
.EOF = 5
...ect.
When the client application uses invokes one of these properties or methods, an ActionCode (as listed above) is sent to the server, along with any parameters that also need to be sent. Your request needs to be serialized and sent down the socket to the server. For example, lets look at the .Open Method.
Typically a recordset is opened in ADO like this:
Set rsCustomer = New ADODB.RecordSet
rsCustomer.Open <SQL Statement>, <Active Connection>
Your class on the client end would have to have two parameters... Your SQL statement and the active connection. So your Open request would look like this:
ActionID=1
strSQL="SELECT * FROM Customers"
When you serialize it, it might look like this:
1;SELECT * FROM Customers
The server side will also have to know that a 1 means Open a recordset. On the client machine, you just program ADO as you always have. Open a recordset and run the SQL statement against it. ADO has a .Save function where you can save the recordset to a file. You can do this and send the entire thing back to the client, or you can leave it open and when the client does a .MoveNext do a .MoveNext on the server and return the record that way. There are serval posabilites on how to implent this functionality.
I would suggest some preliminary architecture and a mapping of what properties/methods you would like to have available and start there. Then start assigning ActionID's/numbers to each property and method. The value doesn't matter as long as the Client and server know what each ActionID means.
Then start implementing base functionality like establishing a connection to the server over a socket. Then close the connection over the socket. Then start implenting opening and closing recordsets, then work on the navigation, BOF/EOF and finally .Add, .Update and .Delete if you choose to have that functionality.
Your connection object will be slightly different from ADO's connection object, since you have to provide an IP address and Service port. Encapsulate this in your code so the programmer implementing your objects doesn't have to how it works behind the scene.
Your connection object might be used like this on the client.
Dim objConn As TCPConnection
Set objConn = New TCPConnection
objConn.IPAddress = "192.168.0.1"
objConn.ServicePort = 921 'Or whatever port you want.
objConn.UserID = "Jim"
objConn.Password = "123main"
objConn.Open
When you open your recordset like this:
rsCustomer.Open strSQL, objConn
You can have the Recordset object retrieve the properties from the Connection object to figure out what database to hit. You can get all the IP and Service port information from the connection object to open your recordset.
Of course things like row level locking and such will be a pain in the you know what...
I've never written this particular functionality, but have thought about it several times. I did write one that was very similar to it where it passed XML back and fourth across the network and it used ActionID's and it worked great! It was really quick too and supported multiple clients connected to the server at the same time.
Good Luck!
-Steve Snaggs
tribesaddict@swbell.net
To define recursion, we must first define recursion.
- Status
