I am a new VB user so please tell me what is the best way to link VB to a robust database. I was just assigned creating a (if/then) form that will link to a database. This form on paper is 13 pages long!!! They have tried an access database but this did not work. Any suggestions? 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 SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Visual Basic linked to a Database 4
- Thread starter Kechi
- Start date
-
1
- #2
I don't know if this will help you with your problem, but the following is how I link my form to an access database;
You need a connection to the database and a recordset, for the purpose of this example i will call the connection "tom", the recordset "bob", the database "bill", and the table in the database "sam"
dim Tom as connection
dim Bob as recordset
' connection to database
Set Tom = New Connection
Tom.ConnectionString = "provider=microsoft.jet.oledb.4.0; data _ source=c:\database\Bill.mdb"
Tom.Open
' Recordset to a table in the database
Set Bob = New Recordset
Bob.CursorType = adOpenStatic
Bob.Open "Sam", Tom, , , adCmdTable
' then you link a field in the database to a variable in the table as follows
' "name" is a field in the database and "txtTest" is a textbox in the database
txtTest.text = Bob!name
That is it, make a connection to each of the fields you want to use in the table in the database and you hve your connection. saving and updating is a whole new problem
You need a connection to the database and a recordset, for the purpose of this example i will call the connection "tom", the recordset "bob", the database "bill", and the table in the database "sam"
dim Tom as connection
dim Bob as recordset
' connection to database
Set Tom = New Connection
Tom.ConnectionString = "provider=microsoft.jet.oledb.4.0; data _ source=c:\database\Bill.mdb"
Tom.Open
' Recordset to a table in the database
Set Bob = New Recordset
Bob.CursorType = adOpenStatic
Bob.Open "Sam", Tom, , , adCmdTable
' then you link a field in the database to a variable in the table as follows
' "name" is a field in the database and "txtTest" is a textbox in the database
txtTest.text = Bob!name
That is it, make a connection to each of the fields you want to use in the table in the database and you hve your connection. saving and updating is a whole new problem
-
1
- #3
What do you mean by a robust database?
Access works very well for 10-20 users and databases under 100 MB. Beyond that, it starts having problems. If you have a significantly larger user base or data size, you should consider SQL server or Oracle. Both of these are much more expensive than Access, and require more knowledge to administer. You can use ADO to retrieve records from either of them.
Is your table structure normalized?
Are you trying to make your 14-page form data-bound to a single recordset, or is it filled in code?
Access works very well for 10-20 users and databases under 100 MB. Beyond that, it starts having problems. If you have a significantly larger user base or data size, you should consider SQL server or Oracle. Both of these are much more expensive than Access, and require more knowledge to administer. You can use ADO to retrieve records from either of them.
Is your table structure normalized?
Are you trying to make your 14-page form data-bound to a single recordset, or is it filled in code?
-
1
- #4
I use Access with a database that is presently about 7 Gig in size. The original database was written in Foxpro 2.5 and it is not normalized.
By using Access, I can get a much more efficient arrangement of the data that I actually need to do a particular operation.
The whole thing is on a network. There are 99 possible users, with an average of 34 concurrent users.
The Foxpro files are peer to peer. The Access is client server (which also helps to cut down on network traffic).
I then use VB for a front end on the client station.
Some of these apps use DAO; however, ADO is significantly faster.
Everything works fine. The major problem is that there just aren't enough hours in the day to get everything done that I would like.
There is some talk about going to SQL Server or Oracle. But, at the present moment, this is only 'manager talk'.
By using Access, I can get a much more efficient arrangement of the data that I actually need to do a particular operation.
The whole thing is on a network. There are 99 possible users, with an average of 34 concurrent users.
The Foxpro files are peer to peer. The Access is client server (which also helps to cut down on network traffic).
I then use VB for a front end on the client station.
Some of these apps use DAO; however, ADO is significantly faster.
Everything works fine. The major problem is that there just aren't enough hours in the day to get everything done that I would like.
There is some talk about going to SQL Server or Oracle. But, at the present moment, this is only 'manager talk'.
-
1
- #5
MatsHulten
Programmer
If you have an Access DB that is 7 gig in size, you should have switched to SQL-server a _long_ time ago! (Either that, or make darn sure the backups are working properly...)
Your problem connecting to it, is most likely due to that Access is not scaled to handle that amount of data, so basically you are locked out. Can you run the question you want to do from VB, inside the Access? If not you should switch immedately as you may suffer data loss/corruption at any time.
Good Luck
-Mats Hulten
Your problem connecting to it, is most likely due to that Access is not scaled to handle that amount of data, so basically you are locked out. Can you run the question you want to do from VB, inside the Access? If not you should switch immedately as you may suffer data loss/corruption at any time.
Good Luck
-Mats Hulten
- Status
Similar threads
- Locked
- Question
- Locked
- Question
