[RESOLVED] How to fill a new .mdb(vb 6.0 enterprise edition)

[Brian M.]

I want to learn to work with db's but I have no idea where to start. I have read alot of articles here, but I am still clueless as to how and when to apply these things I read. Could someone give me some structured instructions starting from the beginning of creating and filling and accessing a db, or maybe point me to a good db beginners tutorial? I am having trouble grasping the proccess here. I know I have to create a db, which I did with visdata. Now, I have to write an app to put stuff into the db and retrieve from the db? I really feel stupid right now, any help would be greatly appreciated.

[RhinoBull]

I want to learn to work with db's but I have no idea where to start...

You can start right here - it's one of the sticky threads in this same forum.

[Brian M.]

Ok, I read everything in there, my head hurts now, and I still have a problem, I downloaded beacon's ado tutorial, great stuff btw, I am starting to get a grasp of this, but my new db doesn't have any tables yet, and I can't figure out how to add them. Do I need to find or write an app that will allow me to add tables to the db? I do not have access or office or anything, so I will have to do this the hard way. I tried to use the add table sql statments in visdata that I found at the sql tutorial site from your link above, but all I got were errors, and no new table. So any help here would be appreciated too. What kind of format would I have to write a new table file in? Couldn't I write a spread sheet app with a form divided into rows and columns and a button to save as a table? And what extension would I have to save it as? And how would I insert it into the new db? I know there is a way to add tables and recordsets to tables with vb. This is a personal db that I will fill with notes and pictures and such as soon as I figure out how to make tables. I have limited room on my hd..

And btw I am running vb 6.0 enterprise with sp 6 and .net 2.0 installed on 98se, if that will help. Please help me and ease my growing frustration with db's. I am really considering text files, or random access files for records, I know how to use those, but I know a db is alot more efficient in the storage and retreival of information, and I can't store files and images in a text file, so I really would like to learn to work with them. My friend gave me the vb 6.0 installation disk, but he lost the msdn disk that came with it so I am having to learn by trial and error, alot of error. Any help will be appreciated here.

[si_the_geek]

Without Access itself it is harder to make tables.. writing an app with Create Table statements or ADOX is probably the best solution (unless VisData can do it for you, or you can find another program that does it).

To be honest, given the amount of effort involved I would go for the free (and in many peoples opinion, better) alternative of SQL Server Express, which also has a free management tool. In the last post of the SQL Server 2005 thread at the top of this forum are links to various downloads for it.


If you really want to use Access, we can help you thru it, but it will probably take a while.. first of all, how did you actually create the database? (are you sure it is a valid mdb file?)


Oh, and for this kind of work I wouldn't worry too much about missing MSDN - unless many of us have missed something, it doesn't cover this kind of thing too well (you'll get better advice from RhinoBull's signature, or the FAQ article). If you want to see the online version of MSDN, see the "Help files" article in our Classic VB FAQs [edit: or the red link in RhinoBull's signature!].

[Brian M.]

Thanks for the response. Visdata does make valid .mdb files, but unfortunatly, after a bit of research, I find out it uses dao connection methods from access97. This multi-faceted world of dbs is rather confusing to a beginner, so thanks for the help. I will go check out the free version of access and see if I can get a handle on it, but I would still like to learn to create tables with vb code tho, I think it will be a valuable skill to learn. I take good notes, so lay it on me. And thanks, again...


Well, I checked out the free version of sql server express, and I will not be able to use it at this time, I am running 98se, unless I can come across the missing dlls that I need to run it. It was a nice thought tho. So, I think I need to focus on dao connections for the time being, its a start anyway. I will be researching dao connection types until I hear back from someone here. I appreciate the efforts.

[si_the_geek]

You don't need to use DAO, and I would definitely recommend against it. Once the database has been created, it is just a database - you can connect to it in any way you like (preferably ADO, as shown in Beacons tutorial).

Assuming you use ADO, to make tables in the database you just need to connect to it using a Connection object, and either run Create Table statements (eg: cn.Execute "Create Table ..." ), or use ADOX instead.

[Brian M.]

Ok I have this code to add a new table. What do I change to set the table name?

Option Explicit
 
Dim CN As ADODB.Connection
Dim CMD As ADODB.Command
 
Private Sub Command1_Click()
On Error GoTo errTrap:
 
Set CMD = New ADODB.Command
CMD.ActiveConnection = CN
CMD.CommandText = "CREATE TABLE SAMPLE(Name1 varchar,Address1 varchar,age1 int)"
CMD.Execute
Set CMD = Nothing
 
Exit Sub
errTrap:
 
MsgBox Err.Description, vbInformation
End Sub
 
Private Sub Form_Load()
Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\briansdb.mdb ;Persist Security Info=False;"
 
End Sub
 
 
 
Private Sub Form_Unload(Cancel As Integer)
Set CN = Nothing
End Sub

[si_the_geek]

First of all, you are missing a very important line from Form_Unload, you aren't closing the connection, which can easily lead to problems (such as database corruption & locking issues). Add this before the Set line:

CN.close

You should do similar with virtually all object variables before setting them to nothing - which includes CMD (and anything else you use Set with).

As to your question... to change the table name (and fields, etc), you just need to change the CommandText. See here for an explanation of the Create Table statement.

[Brian M.]

Ohhhh.. Ok. So those sql querys and such from that site will work with the vb code. I was under the impression that code was for something else. Ok, I thinks I am starting to get this now. A lightbulb just came on. Its getting a bit clearer in here. I am going to go try some stuff with the db, I will be back to let you know how it worked out. Thanks a million!!