DAO problem? 2

[capone67]

Hi Gang

I am working on an ActiveX project where I have used MS DAO 3.6 object library. The project runs fine on my system and another system that has VB installed on it. When I move to a system that does not have VB installed on it I am prompted with a DSN dialogue asking for an odbc driver.

Any ideas on how to fix this?

Ken

 

[HermanvLimbeek]

Hi,

Search Microsoft's Knowledgebase for article Q233002 or click

http://support.microsoft.com/support/kb/articles/Q233/0/02.ASP.

Success,

Herman

 

[capone67]

That solution is too involved for the level of user that will be using my control.

Is anyone aware of another method to install the DAO library?

Ken

 

[capone67]

Just an update.

The dialog that comes up is titled "Select Data Source" and the message says "Select the file data source that describes the driver that you wish to connect to any file data source that reffers to an odbc driver which is installed on your machine"

I am using DAO to create a database on a local system. This was the only way that I could find to create a database at runtime. I use ADO to access my Access 2000 database without a problem.

I really need to find a more elegant solution than having the user download MDAC, then create a folder, copy a file and then register the dll with regsvr32.

TIA

Ken

 

[HermanvLimbeek]

Hi Ken,

If you want to make easy for your user, you 'll have to do some extra work. Here's some sample code:

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim key As ADOX.key
Dim idx As ADOX.Index
Dim Ok As Boolean

Set cat = New ADOX.Catalog

'gsConn looks something like this:
'Provider=Microsoft.Jet.OLEDB.4.0;Datasource=sDBName;JetOLEDB:Engine Type=4;
'Maybe you need to modify the typenumber for the Access 2000 DB. But you already got that.

cat.create gsConn
cat.ActiveConnection = gsConn

sTblName="DBHistory" 'Sample table with fields

'+++++++++++++++++++++++++
'TABEL: DBHistory
'+++++++++++++++++++++++++

Set tbl = New ADOX.Table
tbl.Name = sTblName
cat.Tables.Append tbl

' Create a new autonumber ID Column
Set col = New ADOX.Column
col.Name = "ID"
col.Type = adInteger
col.ParentCatalog = cat ' Must set before setting properties and append column!
col.Properties("Autoincrement&quot = True
cat.Tables(sTblName).Columns.Append col

' Create a new Primary Key for the table
Set key = New ADOX.key
key.Name = "PrimaryKey"
key.Type = adKeyPrimary
key.RelatedTable = sTblName
key.Columns.Append "ID"
cat.Tables(sTblName).Keys.Append key

With cat.Tables(sTblName).Columns
.Append "Event", adWChar, 255
.Append "Date", adWChar, 20
.Append "Time", adWChar, 15
.Append "User", adWChar, 30
.Append "Workstation", adWChar, 40
.Append "Version", adWChar, 20
End With

 

[capone67]

Thanks Herman

I'll give that a try in the morning

Ken

 

[capone67]

Good Morning Herman

I'm well on my way with this ADOX stuff. What has me stumped now is how to set Required=false and AllowZeroLength=true on my fields. Any ideas? Microsoft help is not very helpful.

Ken

 

[HermanvLimbeek]

Hi Ken,

To set the Allow Zero Length property you'll have to use ParentCatalog:

conn.open "Provider=... "
set cat.ActiveConnection = conn
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
Set col.ParentCatalog = cat 'this needs to be set before the next line
col.Name = "Column2"
col.Type = adVarWChar
col.Properties("Jet OLEDB:Allow Zero Length&quot = True
tbl.Columns.Append col
cat.Tables.Append tbl

To set the required property try

dim objCol as ADOX.Column
set objCol = new ADOX.Column
objCol.Attributes = adColNullable

I cannot find another property or attribute which comes closer to 'Required'.

This should work but I haven't tried it yet.

Success,

Herman

 

[HermanvLimbeek]

Hi Ken,

To set the Allow Zero Length property you'll have to use ParentCatalog:

conn.open "Provider=... "
set cat.ActiveConnection = conn
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
Set col.ParentCatalog = cat 'this needs to be set before the next line
col.Name = "Column2"
col.Type = adVarWChar
col.Properties("Jet OLEDB:Allow Zero Length&quot = True
tbl.Columns.Append col
cat.Tables.Append tbl

To set the required property try

dim objCol as ADOX.Column
set objCol = new ADOX.Column
objCol.Attributes = adColNullable 'the column can contain null values

I cannot find another property or attribute which comes closer to 'Required'.

This should work, however I haven't tried it yet.

Success,

Herman

 

[HermanvLimbeek]

Hi Ken,

To set the Allow Zero Length property you'll have to use ParentCatalog:

conn.open "Provider=... "
set cat.ActiveConnection = conn
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
Set col.ParentCatalog = cat 'this needs to be set before the next line
col.Name = "Column2"
col.Type = adVarWChar
col.Properties("Jet OLEDB:Allow Zero Length&quot = True
tbl.Columns.Append col
cat.Tables.Append tbl

To set the required property try

dim objCol as ADOX.Column
set objCol = new ADOX.Column
objCol.Attributes = adColNullable 'the column can contain null values

I cannot find another property or attribute which comes closer to 'Required'.

This should work, however I haven't tried it yet.

Success,

Herman

 

[capone67]

Hi Herman

Thanks for the Allow Zero Length info. Seems it also set the Required field to false on a string. I have one more question for you. I am trying to set up a decimal field. When I use this line .Append "Length", adDecimal which returns invalid type. I tried to use adDouble, 8 and it tells me that the precision is not right.

I need a field that can hold a number that is precise to 8 decimals points. Any idea how I accomplish this?

Ken

 

[HermanvLimbeek]

Goodmorning Ken

The column object supports these properties:

NumericScale
Applies only to columns with a data type of adNumeric or adDecimal. The property sets or returns a byte value which specifies the number of digits to the right of the decimal point (the Scale).

Precision
Applies also only to numeric columns. It contains a long which specifies the maximum number of digits allowed.

Sample:
objCol.Type = adNumeric
objCol.NumericScale = 4
objCol.Precision = 10

And maybe you want to add an index to the table?
After appending columns to the table add the following code:

dim idx as ADOX.Index
set idx.Name = "MyIndex"
idx.Columns.Append "Column1"
tbl.Indexes.Append idx

Herman

 

[capone67]

Thank you Herman

That works beautifully. My problem now is getting it to run on a system that doesn't already have VB installed on it. I have included msrdo20.dll in my package. I have digitally signed the cab file, I have an lpk file as well. And I have also incremented the version number.

Here is a list of the files that are included in the package and deployment wizard
adoxcreatetable.ocx (my control)
msadox.dll
msrdo20.dll
odbc32.dll
rdocurs.dll
vb6 runtime and ole automation

The only thing that my control is doing at this time is creating the db and insterting a table with several fields. It runs fine on any system that has VB installed as well.

Also my digital signature confirmation comes up when the control is downloaded but I am left with a little white box on the screen after everything is done instead of a gray box.

I have made quite a few of my own activeX controls in the past and they have worked so I know it isn't something to do with my packaging. It has to be something that isn't being included or something new with working with ADOX, I even have a reffernce to ADO 2.6 in my project and that doesn't solve my problem either.

Also since I am doing this on a Win2K system I have made sure that I have permission to install software.

Any ideas as to what may be my problem here?

Ken

 

[HermanvLimbeek]

Hi Ken,

you need mdac_typ.exe (Microsoft Data Access Components) version 2.5 or 2.6. On Windows 95 systems you'll have to install dcom95.exe but Win 98, NT and 2000 have it built in.
So download mdac_typ.exe from

http://www.microsoft.com/data,

install it and this problem should be solved too. Check the files included in this package because files like msadox.dll and odbc32.dll are already included. You don't want to redistribute them twice.

Success,

Herman

 

[capone67]

Hi Herman

The thing is I am testing on a Win2K and a Win98 machine and it is not coming up on either but the mdac should be included right?

I only have 2 systems left to be able to do testing with so I am trying to avoid installing other software to make this thing work. This program really needs to be able to install with an absolute minimum of user interaction.

Any other thoughts on what is happening here?

Ken

 

[HermanvLimbeek]

Hi Ken,

I don't know if mdac_typ.exe is automatically included in your project. I use Installshield to install. mdac_typ.exe is not automatically included (not in the version 2.1 I use).
Since the database the tables and fields are created, the ADO part seems OK.
I think the next step would be to determine at what stage in the program the problem arises. Does the program generate an error you can handle? Try to perform another action instead of displaying a gray box?
Do you have the same problem if you perform the same actions in a standard executable instead of an ActiveX control?

I hope this will help you solving the problem.

Herman