I've had a moderate amount of of experience with access databases in vb6.
We'd declare a file system object and from that declare a workspace and from that a record set descriptor.
One could picture and understand what was happening. VS2005 is different though. You could create and delete tables and define them dynamically.
VS2005 seems to be different. It seems that things are done up front in the IDE Almost as if the relationships are drawn. Instructions are to click on this and do that. One creates table adapters and and dataadapers and with these apparoaches everything seems quite removed from what really happening. All of these things are objects.
If I wanted to declare these things dynamically for flexibility where does one begin
What's the vs2005 equivalent to declaring a filesystem object, a workspace and a record set object in code
What is the relationship between a database file and the objects in VB2005
How does one open an access file Does that create a database object Where and how does the table adaper relate Where and how does the data adapter relate If I remember correctly doesn't a data adapter do update and fills and the table adapater contains a host of other preocedure
What does the code look like to open a database and to declare these objects
How does one read a table How are the fields designated
How does one create a table on the fly and define it's fields
ADO/Access Questions
SUJITH NAIR
you're welcome.
exactly!
surely you must be joking!
cheers!
Alphonseyz
Blair,
I didn't understand you to say that, so you're off the hook.
However, I'm looking at ADO.NET. UGH........ It seems to be all SQL based and based on command strings. UGH. That all makes me feel as if I'm not programming. I REALLY don't like SQL.
At any rate, in playing with ADO.NET I don't see anything like a catalogue which has a list of tables in a database. I'm using OLEDB structures as there do not see to be the ADO ways of navigating that I am familiar with.
I used to like ADO but I'm not sure that I like this architecture. This architecture seems to be data-centric and the actual architecture is rather invisible.
{Help militate for emoticons with hair!!!!}
Rick Byers
Blair et all,
I've delved deeply into my class today which does all kinds of dynamic table and catalogue gymnastics. It's quite powerful and I managed to do it without documentaion.
During the day, it suddenly dawned on me that I hadn't seen anything like recordsets or fields. (Duh) I looked to find there are "Views" and they seem very limited.
Then I found there is an ADO forum here on the MSDN board. I looked to find that the kneejerk ADO neophyte response is to write classes for ADOX/ADO/ADO.NET exactly like I'm doing.
Hmmmm. I noticed that this person used very familiar looking code. In other words, I'm sort of on target but only sort of. It appears he used ADOX exactly what I've used it for. It's great for creating databases making tables and catalogue and all of that. But to do real recordset manipulation he used ADO.Net. I think I can understand why
And to top it all of, the recordset constructors and methods are in a third DLL.
At any rate... everything is going as advertised. I'm actually doing I/O to northwind at this point.
Tareq Gamal mahmoud
ahhh. . . this sucks having to type it out.
I will talk in terms of objects and collections of objects. That is discrete data structures that can be accessed programmatically and not necessarily OOP.
A database server is a set of various object collections. One such set is the database object collection. In ADOX, this is called a catalog collection. As you have seen, you can use the activex new methodology to create a catalog via ADOX and add it to the collection. The ADO/ADO.NET dual is executing a 'CREATE DATABASE' sql statement via an ADO/ADO.NET command object.
A database/catalog is also a set of collections. There is an important collection called schemas (I believe thats it) which is basically a collection of collections that belong to a particular user (thats the best way to look at it.) Bear in mind access is not a multi user database so there is only one schema object in the schemas collection. In a schemas collection you will find collections you are familiar with such as the tables collection, and the views collection (querydefs in Access.) Again in ADOX you instance a new table object, add fields to its Fields collection, indexes to its index collection, constraints to its constraints collection, etc. You then add it to the tables collection. The ADO/ADO.NET dual is executing a 'CREATE TABLE' sql statement via an ADO/ADO.NET command object.
This is all for the server side. That is, this is just to get the server database into a state that it can hold your data. Once you have a database server set up, you can use ADO.NET to build a dataset that mimics it for the client side. The process is quite similar to using ADOX against the server. You instance a dataset, you append DataTables to its Tables collection. You append columns to its columns collection. You define relationships between the tables, constraints on columns, etc.
Now you have two sets of data structures - one on the server side in the database application (SQL Server/MS Access/Oracle) and one on the client side (DataSets in your .NET application and/or libraries.) So I guess your question is, how to get data back and forth between the two Thats what the DataAdapters/Table Adapters are for. they map your data. They map DBCommands executed against your server connection to your client dataset.
Or is your question, how do you execute sql against your dataset the short answer is you don't. You can build filters for DataViews against your tables and use the various methods of a datatable/dataview to "find" the specific row(s) you want.
Ankeet
wait a second, renee! I never said that ADOX was anything more that for the creation of database objects.
Once you create you database objects - tables, indexes, views (access queries), use ado.net to maipulate data in them. sorry if there was a miscommunication.
peterneo
CDI2005
the ADO.NET analog to the ADOX catalog is basically the DataSet without the owner 'level'.
But there doesn't have to be a a one to one relationship between database objects and Client side ADO.NET objects. In practice, the database could have one schema and you could define your own schema in your app's DataSet.
And what do you mean you don't like SQL That's like saying "I don't like lightbulbs, I prefer chewing gum!"
Now saying I don't like VB. . . thats a different story. . . that's like saying "I like my hammers made out of steel, not Jell-OR!"
Eudaimonia
Andrew Forman
This is where I will probably get flamed rather than answers that directly correlate to my questions, but I'm a glutton for punishment... I am NOT, in ANY WAY, saying that you're wrong it what you're doing or that any previous answer was wrong. I'm simply providing a different way to approach this.
So here it goes...
The previous posts provided answers to your questions by retro-fitting old technology instead of showing how to do it in the newer technology instead. There is nothing worse than reconfiguring your app (assuming you need this for a corporation or business rather than personal reasons) a year or so later when you could have implemented it in the first place.
I'm going to answer your original question with a question/analogy... Let's say I invented a device that halved the time to completion for building widgets. While this new invention had the same features as the old device AS WELL AS newer and easier functions to get the job done quicker, wouldn't you agree that it was worth the time and effort to learn how to use this new device Now, while the same features exist, they're accessed or called differently from the old device. Same functionality and actually EASIER and QUICKER to do, but different. Wouldn't you still agree that the very short time to learn the new and BETTER way was the right choice Not to sound like a salesman or anything, but...
ADO.NET has the same, improved and additional functions compared to ADOX. Instead of trying to retro-fit archaic functionality in your app, why not investigate HOW to do it in ADO.NET instead.
Since I like to believe that I talk the talk AND walk the walk, I'll give you some samples on how to do it BETTER!
To sum up a couple of your questions into one regarding accessing an Access DB, what I do is (assuming that the STRUCTURE of the DB is the same but different data) create an OLEDB connection to template database and store the DB Connection string in an Application Settings file with the DB Path as a token (for example, I like to use <DBPATH>) and allow the user, through an Open File Dialog, to select the DB. Then I do a replace on the token with the actual file path. As for creating tables or views, what I normally do is use a command to run a CREATE query to do this. This is probably the WORST security-wise way to do things (there is so many "wrong" way to do things nowadays that I worry if I'm doing it the right way even if I actually AM!) but it works and I haven't (knock on wood) had any problems to date.
I am SURE there is a better and cleaner way to do all of this (if so, let us all know!) but it does work. I'm not really sure from your post what your ultimate goal is for this data, but I can tell you that for simple read-only display purposes (a la the ol' ADOX recordset) you can use the datareader and loop through each row like you did with the ADOX recordset. For full data-entry functionality, I would suggest binding the dataset to a DataGridView control and configure it accordingly. To see the code in action, take ADVANTAGE of the wizard to create your dataset then take a look at the code. The rest of your inquiries could be found by looking at the actual DataSet Schema once created.
Peter Piksa
I don't think so.
Windows is the third Dave Cutler system that I've worked on. I began with RSX-11M and was an VMS engineer in the cluster and I/O group. I've written thousands of lines of operating systems code and drivers and named the names and invented the processes and data structures. That code still runs world wide. My projects have always been the most technically high risk in a group of 400 engineers.
The old ADO was OK because I could say RST.Movenext or RST.MoveFirst. That's fine. I like that. I could interrogate the table and know the number of records.
I could say a=rst.fields(cBlair).value and know what I would get. I could picture the workingset datastructures in my head. I couldknow what data or what portion of it was in memory. I could know what was instantly available and I could structure my requests to control that ahead of time.
I am SO uncomfortable handing a command string to some unknown entity when I have no idea what the heck it's going to do. (Keep in mind I have no glossary of sql commands) I've never seen any and I don't know what they do.
I'e programmed in some of the most complicated languages like Bliss-32. But it wasn't case sensitive. I've programmed in C and Java. They are ok BUT I dislike two things about them. Coming from DEC I really dislike case sensitive languages.
I alse dislike the squiggles and voids.....
The other day a fourteen year old MSNed me. He was working on an ASP project and he had a shopping cart object. It wasn't working.
I asked him how large the object was. He didn't know. A week later he wrote to tell me that he had fixed the problem. The object was not being initialized by ASP.
"That's why I asked how large it was", I said. If you had some idea of that or bothered to look at the structures of the object, you'd kknow it hadn't been intialized.
"OOOOOOOOOOOOOOHHHHHH", He said.
Double clicking data diagrams doesn't do it for me because that doesn't tell me a thing about what's going on underneath. How does one possibly optimize
So no, I don't like something that I hand a command string to and it "just does it". What is it doing
But you could help me.
How about a couple of lines of simple code where I get the closest thing to a Catalogue. Once I can do that... I think I can take it from there.
So far I have:
Dim con As OleDb.OleDbConnection = a.OpenDataBase(GetConnectionString)The next thing I want is the equivalent to a catalogue.
And thank you!!!!!!!!
demopro
hey renee. ..
In .Net 1.1, the easiest was to interop ADOX. see: HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET and HOWTO: Create a Table with Primary Key Through ADOX. You can aslo execute Jet DDL against any jet database. Begin here. Just remember to routinely compact your access databases!!!
There may be an easier way in ADO.NET 2.0, but I don't know.
Remember, the XXXDataAdapter is just a traffic cop between the the commands that interface the connection, and the dataset that the commands act upon. Yes, in the ide, you get to neatly "type" your dataset, but working with the untyped dataset tables is not unlike working with the old recordsets.
There is the command builder to help spit out sql, though it can be somewhat limited.
You can dynamically build relation and constraint objects between tables in a dataset based on the the results of a OleDbConnection.GetSchema method. If you are creative you can figure out someways to do this efficiently and universally.
Using DataSets in ADO.NET
A book that helped me alot was ADO.NET in a Nutshell granted its 1.1. Frankly, I have not found much in the way of ADO books in any flavor.
Cheers
Hepper
Blair thank you soooooooooooo much for those references.
I was able to quickly put together a class using ADOX and ADOX makes sense!
Also the references on ADO.NET made a lot of sense and now at least I understand it. In reading about ADO.Net I was amazed to find that ADO.Net is mostly in memory datastructures and to fully use ADO.Net the dataset is serialized as a blob and written out by a stream writer. Goodness I appreciate the richness of the primitives in ADO.NET.
Speaking of ADOX, one thing about classes always occurrs for me. I'll quickly put a file class together. Thing it begins to feel less than fully flexible. To make it really flexible it becomes more and more complex until it's full of primitives just like the class it contains. I find that to achieve the flexibility that I really want, I'd have to write Access itself into the class. Do you know where Access datatypes are documented What is the Chapter datatype When I did my VB6 Access work, I used the memo datatype for storing html. I didn't imediately see any ADOX equivalents.
I love .NET but file I/O has not been made easier because of streaming I don't believe. I really liked the flexibility of the old scripting file system object.
I marked this as answered but I don't think the thread need be closed there is an awful lot to discuss.
ramarao
As a quick follow-up to my previous post, take a look at this article. It might not be exactly what you're trying to accomplish, but it should get you on the right track...
http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnadonet/html/genericdacode.asp
Access is, more or less, a SQL-based database. You had to use (unless you just used table objects) some sort of SQL code in ADOX to get the data that you wanted and this is no different. Like I said previous... it's there, but just a different way of accessing it.
Lastly, there's an old saying that goes "A million Elvis fans can't be wrong", which basically implied that if you hated his music and said as much then how do you explain why everybody else disagrees with you In your case, forgot about how you USED to do it... Just worry about how to do it NOW! Once you start comparing the old way to the new, you won't get far. I swear to you that it is not only better but easier and quicker in VB 2005 than VB6. Once you can accept that, then the rest will be easy!
Good Luck!
Mainiac007
surely you must be joking
No, I'm not. When you consider what basic I/O looked it in with the fileopens gets, puts and writeline statement, an object oriented approach with defined properties were quite an advancement not to mention that the scripting library would do all kinds of things like getting file attributes, move files and create file and get file sizes.
I really liked scrrun.dll. I did notice however that at times, with some calls applications that used it would trip antivirus alarms which I thought was a little aggressive on the part of the AV. I mean, what's a system programmer to do
{Help militate for emoticons with Hair!}