Using VB6 for Access or MySQL DB

[Access2007User]

I dont know how to use VB6 yet but I'm learning VB6.

In Access, you create your query objects by referring to the table objects.

In VB6, how do I create the SQL statement when I dont have the table objects? How do I create a code to create SQL statement?

Can someone give me idea or code example how to do that!

 

[DCrake]

When you first open the project you need to establish a connection to the Access mdb. The easiest way is using DAO.

Dim MdbConn As DAO.Database

Set MdbConn = OpenDatabase(Path to your mdb)

Then use DAO.RecordSet to refer to tables and queries.

 

[Access2007User]

I really appreciate your response DCrake but will you kindly extend your assistance and complete how to refer to tables and queries using dao.recordset. I want to be able to use the aclink. Can I?

 

[DCrake]

In VB you do t as such have a dashboard where you can link tables as you can in Access. Having said that there is a section where you can add a data environment. I tend to leave this alone.

Taking my previous post into consideration. You would use Access to create your tables and queries then in VB refer to them as DAO.Recordsets

You can also do SQL such as

Set Rs = MdbConn.OpenRecordset("Select * From Table Where Field = " & Criteria)

If Not Rs.EOF And Not Rs.BOF Then

    Do soomething here

End If

Rs.Close
Set Rs = Nothing

It is very much like using Access VBA except you are using VB.

here is a link to a website that I use regularly for any VB samples.

http://www.planetsourcecode.com


David

 

[Access2007User]

thanks DCrake. I have also looked upon the linked you provided but it was so vast, I have to see them one by one. Though I am doing some.

Anyway, can I ask follow question.

The code you provided was only for one connection object and a recordset for that connection, how about if I have more that one connection object, like 3 database, should I make 3 connection objects? and how will I make the recordset for those 3 different individual connection objects?

TIA

 

[DCrake]

If you have 3 different mdbs and want to connect to them all, the best solution would be to create 4th and link all the other three together then conect to the fourth. Only issue is if yu have matching table names.

David

 

[Access2007User]

the 3 database have different table names. Do I need to create the 4th. I am using VB6 here to create an executable file DCrake.

I was wondering if I can set global variables to make 3 connection objects and also global variables for the recordset objects from those connection objects so that I can use them publicly in my program. Can I do that? That was just my idea yet.

 

[DCrake]

If the information in one mdb is not dependant on the information in another mdb then in theory you could create 3 seperate connections. And use them as required. Whay need to remember is to create and open the connection(s) when you load the project and close them when the application closes.

When setting Recordsets remember to close them immediately after use and release from memory by using the Set Rs = Nothing after the Rs.Close

Sample Syntax

Dim Rs As DAO.Recordset

Set Rs = MasterDbConn.OpenRecordset("Select * From Table")

If Not Rs.EOF And Not Rs.BOF Then
   Do Until Rs.EOF
      Some code here
      Rs.MoveNext
   Loop
   Rs.Close
End If
Set Rs = Nothing

Notice the position of the Rs.Close as you can see it is inside of the If Not EOF statement. This is becuase you cannot use the .Close on a recordset that has no records.

'*************************
'BOF - Beginning of File tag
'*************************
'Record 1
'Record 2
'etc
'*************************
'EOF - Enf Of File tag
'*************************

David

 

[Access2007User]

ok. I love it DCrake. it was a straight answer to my idea.

On the second hand, with Access, it is just simple to get the data from the other access database by linking the tables and you dont need to release or delete the link immediately. Thereby, you can use those linked tables to make queries and or recordsource of a form or report.

However, in VB6, I need to create connection and recordset objects and everytime I am finished I need to release it from memory.

Can I just release it from the public variables just before my application closes so I dont need to repeatedly make variables for the recordsets?

 

[DCrake]

Lets say one of your mdbs contains a list of all you lookup tables and as such will not need to be amended. What you can do is

When application loads

Dim LookupDB As DAO.Database
Dim RsCategory As DAO.Recordset

Set RsCategory = LookupDb.OpenRecordset("TblCategories")

You can then refer to the RsCategory for the full duration of the session, then when you close the application

RsCategory.Close
LookupDb.Close
Set RsCategory = Nothing
Set LookupDb = Nothing

if you want to be really clever and release resources even further you can pass the PK and Description in your categories table to an array and use the array in your application therefore as soon as you populate the array you close the recordset and the db. This depends on the tables and how you are using them though.

David

 

[Access2007User]

wow I love it. but you said that is for no need to amend but just lookup. But more often I need to interact and update or add new records to the recordsets. I think it can also be used, am I right?

 

[DCrake]

If you think that the table may have been amended at any point during the session, then you need to be thinking about opending and closing the recordsets when used. As this will automatically refresh the data in your recordset as you open it.

David

 

[Access2007User]

wait, what do you mean about amend? amend to the table structure or to the records therein?

I am presume we are talking about amendments or updates to the records in the table.

quote: "If you think that the table may have been amended at any point during the session, then you need to be thinking about opending and closing the recordsets when used. As this will automatically refresh the data in your recordset as you open it."

Why the need to open, close and reopen again? Isn't it suppose to be automatic just link "AcLink" in Access?

 

[Access2007User]

same post as above please. thanks.

 

[DCrake]

You are corerct in assumption that I was talking about the records and not the structure. The point about opening and closing the recordsets is clear. When you open a table/query as a recordset it is only a snapshot of how it looked and what it contained at that point in time. If someone added a records 5 minutes later and then in 10 mins you used the recordset the new record would not appear.

This is why the need to open and close when required is apparant. Also leaving multiple recordsets open can take up a large section of your resources, especially if they are large recordsets.

David

 

[Access2007User]

I see. so creating connection and recordset objects are just like snapshot.

So therefore it is not linked tables that the records the flowing thru the link. Am I correct thinking here? Is there a way to make it like that? is dynaset proper term?

 

[mcdhappy80]

here is a link to a website that I use regularly for any VB samples.

http://www.planetsourcecode.com


David

Hey, thanks for the awesome site DCrake, cheers!