Thinking about creating new utilities for the Tools menu
1. Database builder
would allow user to create mdb file and add tables and fields directly, or
generate the code to do the same so it can be pasted in a file, or
both.
2. Create an SQL query based upon selections from multiple dropdowns and then have the proper coding automatically generated so it can be pasted into an application
LarryMc,
That sounds great. I'm interested in databases, and I
use a few. Always looking for a better one.
Looking forward to what you come up with.
Have a good day,
Bill
Attached is my first stab at the layout for the database builder utility
This would be the dialog for creating the tables and fields in a mdb file.
As in all alphas, things are subject to change
comments are welcome.
Hey Larry.
It's been quite awhile since I did any programming with IWB, so I really don't have a horse in this race... BUT... I would think that any tool to assist with working with database projects would be very welcomed! Once upon a time, I would have offered a gigantic neck hug and $$$ for such a tool. :-* :D ;D
As a general comment though, I never really understood why the MDB format was chosen for use with IWB in the first place. My (unheeded) argument has always been that SQLite is almost the defacto standard for desktop DB applications, is well supported and still allows the data to be used -as is- on many other platforms if required.
-Doc-
Placing this little project on hold for now.
The reason is that I need to finish the help file for the IWBasic2.5 beta.
This topic was in the IWB2.5 board and I've moved it over here to the Database board.
It's still on hold because I still need to finish the work on IWB's help file.
But what I did do is spend and hour or so trying a little different approach.
In my 1st layout I was using a listview to hold the names of the tables in the database and another listview to hold the field information. for each table as the table was added/selected.
I got to thinking; I was going to have to really jump through some hoops to make editing things work the way I sort of envisioned.
Then DUH!!! It seems this fella spent a great deal of time writing this whatchamacallit called IWGrid. Maybe I should give it a try.
Below is a screen shot of the GUI using 2 grids in place of the 2 listviews.
Like it!
Will there be the facility to select which driver you wish to use?
Brian
I haven't allowed space for it in my GUI but sure, why not.
Quote from: LarryMc on December 26, 2014, 06:26:23 AM
I haven't allowed space for it in my GUI but sure, why not.
The GUI would look something like this
Good stuff!
I know you're only messing, but shouldn't "Select proper driver..." read "Select appropriate driver..." ?
I know I'm being pedantic, but you can let me off, seeing its Christmas!
Brian
Quote from: Brian Pugh on December 26, 2014, 07:35:12 AM
I know you're only messing, but shouldn't "Select proper driver..." read "Select appropriate driver..." ?
??? ??? ???
Santa should have brought you some glasses.
Ha ha - my eyes aren't that bad!
;D ;D ;D :o :o :o ::) ::) ;D ;D ;D
After playing around a bit I decided that in this case my IWGrid wouldn't do EXACTLY what I wanted it to do so I went back to using a customized listview setup.
The attached screenshots represent the way the utility is going to look. I'm pretty much locked into this now.
So now I'll start working on the internal mechanics of what happens when.
I think it will be pretty neat when I get through.
I know from previous experience that different drivers sometimes require additional parameters in the DBCONNECT statement.
If everyone would post those they have used or those they are aware of the more I will be able to incorporate into the utility and the more useful the utility will become.
Hi, Larry,
Happy New Year to you and your wife - hope you are well
Here's some that I've used before, and some that were "on file"
Bit of a minefield, as the strings can vary quite a lot. I would be just inclined to do a basic connection string,
based on the type of driver that has been selected - you can't cater for every eventuality
The 32-bit drivers on 64-bit PCs are still there, even though they might not show in the
Data Sources control panel
Looking good, though!
Brian
pDB=dbConnect("Microsoft Text Driver (*.txt; *.csv)",schemaPath,"")
pDB=dbConnect("Microsoft Excel Driver (*.xls)",fileName,"ReadOnly=0")
pDB=dbConnect("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)",startPath+fName61,"ReadOnly=0")
pDB=dbConnect("Microsoft Access Driver (*.mdb)",mdbPath+"BookShop.mdb","",win)
pDB=dbConnectDSN(\"XYZ CORP\",\"UID=President;PWD=Private\")
pdb=dbConnect("Microsoft dbase VFP Driver (*.dbf)","c:\\_TMG Projects","SourceType=DBF;Exclusive=No;Collate=Machine")
pDB=dbConnect("SQL Server","","SERVER=s06.winhost.com;DATABASE=DB_44925_test2;UID=DB_44925_test2_user;PWD=*******")
pDB=dbconnect("Microsoft Excel Driver (*.xls)","c:\gppgmlib\JWalk.xls","DriverID=790;FIRSTROWHASNAMES=0;READONLY=FALSE")
pDB=dbConnectDSN("TMG","")
Thanks Brian
I haven't given up on the utility. With my medical problems(and my current vision problems) I have only been doing the bare minimum of programming.
When I get my vision corrected I'll try to get this done.
I hope you have not given up on this project. I think it would be worthwhile for many of our more inexperienced users.
Willing to help in any way I can.
Bill
To tell the truth I had forgot all about it.
I've got one more woodworking project I'm working on (a rocking chair for a friends 2year old daughter) and then I'll see if I can get back to it and get it finished during these hot west Texas summer days.
Thanks for the reminder
Love the sun, can't stand the heat. In the 100's here in Ft Worth.
Bill
Same here in Abilene
Well, it's taking me a while to get back up to speed; 1) up to speed in coding again (it's been a while) and 2) on this specific project that I haven't worked on in almost 4 years.
I figured out what my stumbling block was before. How best to handle the following from a coding standpoint:
Have 2 lists
the 1st list is of the table(s) in a given database and consists of one column
the2nd list contains a list of the fields in the table selected in the 1st list. This list contains 3 columns.
The 1st column is the field name, the 2nd is the field type and the 3rd is the field size.
As it turns out the best means of implementation is to use 2 listviews.
The 1st listview (for the tables) is modified so that when a cell is selected for editing a subclassed edit control is superimposed over the cell for editing the entry and initialling the field list.
The 2nd listview(for the fields)is modified so that when the 1st cell is selected for editing a subclassed edit control is superimposed over the cell for editing the name entry.
when the 2nd cell is selected for editing a subclassed Combobox control is superimposed over the cell for selecting the type entry.
when the 3rd cell is selected for editing a subclassed edit control(restricted for numerics only) is superimposed over the cell for editing the size entry.
I now have that part constructed.
Now begins the part of what happens when, like:
switching between the editing of 2 tables
making sure a field is completed before moving to next field
and that's not counting the creating of code and the actual files
Need a little help to save me some time
QuoteCOUNTER [(1,1)
BINARY
CHAR
DATE
DOUBLE
FLOAT
INTEGER
LONGTEXT
SMALLINT
TIME
TIMESTAMP
VARCHAR
MEMO
I need to know if this is a complete list of all the field types I need to be concerned with initially.
I need to know which fields have a fixed size and what that size is so I can try to automate that entry.
If there is a max for any variable entry I need to know that.
And as a bonus
If someone can write a typical code string for creating each of the fields I would appreciate that also.
Any and all help would be appreciated.
I just interested in getting this utility working with .mdb files first; then I will look at addressing other file types.
Again, thanks in advance for any and all help.
Larry, I found this site which describes the fields, if that is any use to you.
https://eggerapps.at/mdbviewer/docs/en/field-types.html
Andy.
Larry,
Here's my open database sub for a program that is being used daily by a local school.
There will be some commands of interest in there for you
Brian
You might want to add PRIMARY KEY to your DB Create program.
Since there is a PRIMARY KEY field, which can easily be placed on a single field, there is also the more complex multiple field PRIMARY KEY, which can be done:
iStmt="CREATE INDEX [Primary Key] ON recips (account,recip) WITH PRIMARY"
The account and recip fields are INTEGER, but I need both to make the table unique.
I know tables can be created with out a PRIMARY KEY, but there are times when you want the table to be unique. The fields created using COUNTER(1,1) are often used as a key.
Just a thought to add to your DB Create program.
Bill
I noticed the INDEX creation in what Brian posted and knew I needed to address that.
Just don't know exactly HOW I'm going to handle it yet.
Thanks for posting the reminder so it is easy to see.
I've gotten involved in several projects around the house for my wife (which take priority over everything else) and it appears it is going to be quite some time before I'm going to be able to get back to this project.
Is there anyone out there who is interested in taking my source code to-date and taking over this project?
I am not sure I am qualified to take over, but would like to collaborate with you. I could set up a Dropbox directory that both of us could have full access to, so you could see what I am doing and maybe point me in a better direction, if needed.
Bill
Quote from: billhsln on July 06, 2018, 09:47:14 PM
I am not sure I am qualified to take over, but would like to collaborate with you. I could set up a Dropbox directory that both of us could have full access to, so you could see what I am doing and maybe point me in a better direction, if needed.
Bill
Set it up Bill and we'll see where it leads us.