A little help please??

First off, let me just say that I would've posted this to the Connectivity
NG, however, it doesn't seem like a lot of people hang out over there so
that's why I've posted it here.

With that said....

I've recently been told that DAO is not the best to use when it comes to
databases, so...
I'm making the switch to ADO and my first question is this: I've got a
table called CATEGORIES. Within that table are 3 fields ID (Primary key),
NAME (text) and NAME_ID (number).
I'd like to fill a ComboBox with the NAMEs of the various categories, but
order them based on the NAME_ID and I can't quite get the syntax correct
with ADO.

Here's what I've got so far...


Dim Cnxn As ADODB.Connection
Dim rstCategories As ADODB.Recordset
Dim strCnxn As String
Dim strSQLBlocks As String

' Open connection
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\CADD
Development\Database\ADO_Database.mdb"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open Blocks table
Set rstCategories = New ADODB.Recordset
strSQLCats = "Categories"

rstCategories.Open strSQLCats, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable

Do While Not rstCategories.EOF
Debug.Print rstCategories!Categories
Debug.Print rstCategories!id
cboLibrary.AddItem rstCategories!Categories
rstCategories.MoveNext
Loop

Cnxn.Close

 

That just gives you the contents of the table in "natural order". To
sort, you need a query which includes the ORDER BY clause:

strSQLCats = "SELECT * FROM Categories ORDER BY name_id"

 

Thanks Frank!

One other question... Can ADO create a NEW database complete with tables and
such?? If so, do you have any simple examples or know of any *good*
websites that show how to do this??

Thanks again!

--
I love deadlines
I like the whooshing sound they make as they fly by.

| Matt W wrote:
|
| > strSQLCats = "Categories"
|
| That just gives you the contents of the table in "natural order". To
| sort, you need a query which includes the ORDER BY clause:
|
| strSQLCats = "SELECT * FROM Categories ORDER BY name_id"
|
| --
| There are 10 kinds of people: those who understand binary and those who
| don't.

 

When you say "database", do you mean an MDB file? The answer to that
question is no.

 

Yes
Ok
Thanks

--
I love deadlines
I like the whooshing sound they make as they fly by.

| Matt W wrote:
|
| > One other question... Can ADO create a NEW database complete with tables
and
| > such??
|
| When you say "database", do you mean an MDB file? The answer to that
| question is no.
|
| --
| There are 10 kinds of people: those who understand binary and those who
| don't.

 

Actually, the answer is Yes. I believe the reference you need is
"msadox.dll" or
"Microsoft ADO Ext. 2.? for DDL and Security"

Use the Catalog, Table, and Column interfaces. The following Object Pascal
procedure creates an MDB file with one table in it. You should be able to
translate the syntax over to VB fairly easily; just make note of what
methods are called and the arguments used.

procedure CreateNewMDB;
var
cat: ADOX_Catalog;
tbl: ADOX_Table;
CurrentColumn: ADOX_Column;
begin
cat:= CoCatalog.Create;
tbl:= CoTable.Create;

// create the database
cat.Create('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=NewDatabase.mdb');
// create the table
tbl.Name:= 'PlasticSheetInventory';
tbl.Columns.Append('part_number', adVarWChar, 32);
tbl.Columns.Append('quantity', adInteger, 0);
tbl.Columns.Append('type_of_plastic', adVarWChar, 128);
tbl.Columns.Append('length', adSingle, 0);
tbl.Columns.Append('width', adSingle, 0);
tbl.Columns.Append('thickness', adSingle, 0);
tbl.Columns.Append('remarks', adVarWChar, 256);

tbl.Columns.Item['part_number'].Attributes:= adColNullable;
tbl.Columns.Item['quantity'].Attributes:= adColNullable;
tbl.Columns.Item['type_of_plastic'].Attributes:= adColNullable;
tbl.Columns.Item['length'].Attributes:= adColNullable;
tbl.Columns.Item['width'].Attributes:= adColNullable;
tbl.Columns.Item['thickness'].Attributes:= adColNullable;
tbl.Columns.Item['remarks'].Attributes:= adColNullable;

// add the table to the Database
cat.Tables.Append(tbl);
cat.Tables.Refresh;

// allow a zero length string for the remarks column
CurrentColumn:=
cat.Tables.Item[''PlasticSheetInventory''].Columns.Item['remarks'];
CurrentColumn.Properties['Jet OLEDB:Allow Zero Length'].Value:= True;

//done, so set them to nil
tbl:= nil;
cat:= nil;
end;

Good luck from here,

-Rick Francken