VB6 with Access in Local Network (LAN)

[sha123]

Hi experts..

i developed a simple program in vb6 with access database. i want to connect in a LAN network(3 node). i see some examples using Winsock. that working, but i want work this program in a single system as well as network.. how can i set this program. how can i connect to my database when it work in as single system as well as network server.. thanks in advance

[Doogle]

In your Connection string use something like "\\Servername\Directory\Database.mdb" as the Database FileName and Path. Where 'ServerName' is the name of the PC that's hosting the DataBase, 'Directory' is the folder / path where the data is and 'Database' is the Database name.

[dilettante]

You have to create or use an existing file share that all users have nearly full access to (most permissions except for execute). You can't just name a server and a folder, since that isn't correct UNC syntax even if it looks similar.

Connection strings are normally set up as part of program installation for "real" programs, which is why they really belong in INI files or registry keys instead of hard-coded. The installing admin could specify where the shared database is located during each client install, either through an interactive installer or as a first-run program action, or users might define it using a "settings" dialog in the program.

Even using INI files you shouldn't ask users to modify the file by hand. Normally they should be stored in an installer-created application subfolder of the [CommonAppData] or [LocalAppData] special folders.

[sha123]

I tried this way.. i installed same program in Client and server and and client database path set like as "\\server\data\databse.mdb". its working..but a problem is that when the time of saving and reading data from databse client program take few seconds. its hanging.. how can i solve it. can i solve this problem using Winsock Control. how winsock control use for this purpose..

[jggtz]

If you want to know more about the winsock control, see ---> http://www.codeproject.com/Articles/...-using-Winsock

[naveed217]

You just add
DoEvents
in the code and you will see a much better performance

[sha123]

Hi ..
i modified my codes.. and one of my data base table contains 1lack more records.. when i select or delete some data in this table. few seconds taken..

example RsMain.Open "SELECT ItemCode,ItemName FROM Inventory WHERE ItemGroup=1"
RsMain.Open "DELETE * FROM Inventory WHERE EntryType=1 AND EntryNo='55'" ' (user specified bill no)

if i use DoEvents method, another events working is correctly. but, Recordset not completed. when i call same function an error occured. "recordset is already open". how can i overcome it.. how can i take Recordset data in to a variable?? help me pls..tanxs in advance

[jggtz]

Is it valid to open a recordset using a Delete as a query???

[DataMiser]

No it is not, you should use a Connection.Execute method to do update, delete or insert queries. open recordset should be used for select queries only.

There should be no need to use Winsock, the ADO code and the database engine is prefectly capable of handling the job.

[techgnome]

Sigh... first... DoEvents isn't the answer here.

Secondly... it sounds like you have some design problems there too... and maybe lacking some understanding in how ADO works or should work.

Also, this isn't the best or even recommended way to perform action querries:
RsMain.Open "DELETE * FROM Inventory WHERE EntryType=1 AND EntryNo='55'"

You should look into the Command object and the .Execute method to perform those (action queries are SQL statemetns that don't return data but perform an action... insert, update, delete....)

-tg

[sha123]

i tried this ways.. working is properly in server system ( Data Base contained computer) . but in Node take few seconds.. what is the actual problem.. How can i solve it. My database up to 300MB. i think when i call this function in node database fully loaded to node. so occur the delay.. how can i overcome it. if i use winsock is the problem solve or not??. when my database is low Size MB its working fast..

[DataMiser]

Winsock is not going to solve the issue, it would likely be slower as you still have to do the same operations and then transfer the data and receive and process it.

So far you have only shown 2 lines of code so we can't really tell what you are doing other than trying to use a recordset object that is already open and trying to use a recordset object improperly to run an action query.

[techgnome]

Well with a database that size, first thing I'd do is reconsider using something else other than Access... the next thing I'd do is check the code where you are selecting data... are you running queries like this? SELECT* FROM SOMETABLE .... with no where clause? If you are... don't. you should only be selecting the cols you need, and only the rows you need... otherwise all of that data has to come down over the network. That would be the next thing to look at... network traffic... how stable it the network? blah, blah blah...

-tg

[sha123]

I see some programms.. that working properly and it use same exe for node and server... its working same speed in node and server... how can i write an same exe for node and server.

[DataMiser]

There should be no issue with using the same exe on any pc on the lan. As for speed that depends on what you are doing but it is always going to be faster when the exe is running on the same PC as the database the reason is that you are not hampered by the network. When the exe is on a different PC than the database then the data must be transferred over the network whereas when they are on the same system it does not. This can make a minor or a major difference in speed depending on the speed of the systems, the speed of the network, network traffic load and your code.

I always use the same exe or rather a copy of the same exe on all pcs and in some cases I install on all PCs but place the actual exe on the server and have each workstation run it from there which simplifies the process of updating the software but even in that case the actual execution still takes place on the local PC and the data stream over the network is still an issue if you are transferring large chunks of data.

If you are seeing a large speed difference when using the same exe then either your network is slow or you have written yoru code in such a way as to have it pulling large chunks of data across the network.

[gibra]

I see some programms.. that working properly and it use same exe for node and server... its working same speed in node and server... how can i write an same exe for node and server.

I have made programs VB6.0 + Access (database) that run for years in multi-user (LAN and Terminal Server) without any problem. Some even with 15 concurrent users.
My suggestions here:

1)
This problem is due to cache updating, that in JET has been changed. The default time refresh occur each 5 minutes.
Therefore, if you read data immediately after updating, the data seem no changed.

You need to force the refresh of the cache after each UPDATE or INSERT SQL command (or ADODB.Recordset Update methods, also):

    ' note that Microsoft Jet And Replication Objects Library 2.6 need installed.
    ' library file: MSJRO.DLL (however already installed on most of systems)
    '
    ' CN is the ADODB.Connection 

    Dim Jet As Object
    On Error Resume Next
    Set Jet = CreateObject("JRO.JetEngine")
    If Not Jet Is Nothing Then
         Jet.RefreshCache CN
    End If

After refresh cache, you get the updated data immediately.
I.e., to get the ID set as counter field (Autoincrement) for last added record:

First use the above code, then inquire to get the new ID 
Set rs = CN.Execute ("SELECT @@IDENTITY AS newID FROM MyTable")

2) you do not need to use Winsock to access the data.


3) In multi-user environment it is necessary to implement a strong control about concurrency. See:
Using Optimistic Concurrency
http://msdn.microsoft.com/en-us/libr...cz(VS.80).aspx


4) Wher INSER or UPDATE data, use always Transactions!!!


5) Compact and Backup database every day.


You can downlaod my project that contains a complete application-template (starting from LOGIN) where you can see many of above feature.
I use classes to access database (Get and Save methods)
Unfortunately for you, my page is on italian language, but the source code is VB6.0.
http://nuke.vbcorner.net/Progetti/VB...T/Default.aspx

[Bobbles]

gibra,
Thanks for your post.
I went to your web site and managed to translate it.
I then tried to download the project, and it told me that only registered members can download it.
I have attempted to register about 5 times, but it is a fair challenge trying to interpret the error messages.
The point that I have finally reached is complaining about my password (I suspect).
I cannot translate (or understand) the special characters part of the error message.
My password contains upper and lower case letters, and a number.
It has no spaces, and no special chars
Is that acceptable ?

Thanks,
Rob
PS The OP has shown little gratitude (nor info) for those trying to help.
Did he Resolve the issue ?
PPS I originally tripped across this link whilst Googling to see how to work out how my EXE on every PC, can locate the Access DB on the "central" PC (what path should I store in the Settings.ini located on the user's PC.
Does your example shed light on that area ?

[Magic Ink]

Apologies for being off topic.
gibra,
Yes further to Bobbles post above. I too have had trouble getting to stuff on your website, I seem to remember I registered but still couldn't get anything down. I was after your VS6 Installer about a month ago. I gave up in the end, what a shame ...if it is free in the end why make the website UI so difficult.

[dilettante]

Er, the use of Jet.RefreshCache is woefully misdescribed above and will not do anything useful.

How To Synchronize Writes and Reads with the Jet OLE DB Provider and ADO describes how to do this properly in those rare cases when you need to.

[gibra]

@Magic Ink
@Bobbles

There are no problems on my site.
There are over 8600 registered users, and 5-10 new users every day.