Add Field to Table in Access MDB having a UserLevelSecurity MDW file

[clickman]

Problem Statement w/ Background

I have an existing legacy vb6 program that interfaces with a MS Access .mbb file [MyDatabasePath] that has an associated User Level Security file [DataBase_MDW_SecurityFilePath] . -

Thus to get into this DB, the code provides the database itself in a full path string, AND the User Level Security 9mdw) file in a full path string, AND the UserId and PW .

The code I am working with in the legacy program (which has worked for a loooong time just fine) adds and delete records using the following connection Syntax:

''<Variation for setting the Connection Properties (http://support.microsoft.com/kb/q191754)>

'
'   m_adoConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
'   m_adoConnection.Properties("Data Source") = MyDatabasePath
'   m_adoConnection.Properties("Jet OLEDB:Database Password") = "XXX"
'   m_adoConnection.Properties("User ID") = "YYY"
'   m_adoConnection.Properties("Password") = "ZZZ"
'   m_adoConnection.Properties("Jet OLEDB:System database") = DataBase_MDW_SecurityFilePath
'
'   m_adoConnection.Open
'
''</Variation for setting the Connection Properties (http://support.microsoft.com/kb/q191754)>

I am now faced with the situation of adding new fields to the TABLES in the underlying database [MYDatabasePath] - the .mdb file. Yes, I can open the (.mdb) database in MS Access Office 365 (by using a Command line with switches to specify the workgroup .mdw file) and then from within Access, add the new fields, but that is a PITA and I am looking for a vb6 programagatic way to add the new fields to the underlying database.

I dug around and found a neat little example of how to do this for a non protected database.

[insert link here] https://developerm.wordpress.com/201...ble-using-vb6/

Dim DB As Database
Dim RS As Recordset

Private Sub Command1_Click()
Call UpdateTableField("Table1", "RoomID2")
End Sub

Private Sub Form_Initialize()
  Set DB = OpenDatabase("F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb")
End Sub


Sub UpdateTableField(tb As String, new_fld As String)
Dim CHECK As Boolean
CHECK = False
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String


   Set tbl = DB.TableDefs(tb)
    For Each fld In tbl.Fields
        If fld.Name = new_fld Then
            CHECK = True
        End If
    Next
    
If CHECK = False Then
        Set RS = Nothing
        With tbl
        Set fld = .CreateField(new_fld, dbText)
        fld.DefaultValue = ""
        .Fields.Append fld
        MsgBox "new Field Created !, check table"
        End With
 Else
 
       MsgBox "Field already exist !"
End If


End Sub

This is a nice utility - but again, I am faced with doing this on an .mdb file that has a UserSecurityFile.mdw.

Try as I might, I can seem to get the right string to change out in that OpenDatabase Statement

Set DB = OpenDatabase("F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb")

I have tried

Set DB = OpenDatabase("C:\Users\MyName\Desktop\MYDatabaseFile..mdb") / WRKGRP '"MySecurityFile.mdw"

with all sorts of “” and , and ; at vaarious places but I get Syntax errors.

I have tried using the same structure as we use in our main code base to add records

and changed the Set DB to

Set DB = adoConnection.strConn

but I throw a Run Time 91 Error an object block variable not set

in the line ..

 Set tbl = DB.TableDefs(tb)

I'm going banannas .. Ideas .. It has been a while since I have worked with the connections and opening of databases and so I ask for help now! I think I am connecting but I am not opening and that is the reason for the bust?

Advice?

[ChrisE]

you are mixing DAO and ADO

so which is it ?

to open a mdw it look somthing like this

 Dim cnn As New ADODB.Connection

   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
   cnn.Properties("Jet OLEDB:System database") = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"
   cnn.Close

to alter a Table you can use sql

strSqlA = "ALTER Table Adressen ADD Column myNewField varChar(35) Null"
cnn.Execute strSqlA

hth

[dilettante]

You seem to be trying to mix ADO with the ancient DAO.

Does this database of yours use Jet's Share-Level Security, User-Level Security, or both at once?

If you have User-Level Security then you'd have to use an account that has the right to modify table structure.


Ideally you would read through the article "Advanced Microsoft Jet SQL for Access 2000." You can find this in your October 2001 MSDN Library CDs which you ought to have installed as the online Help for VB6 anyway. This was the final edition supporting VB6 and the best one. Earlier editions may or may not have that article depending on just how old they are.

That article along with its companions "Fundamental" and "Intermediate" are well worth adding to your Favorites list there for quick access.

I found a... snagged copy at https://blog.pagesd.info/public/2006...for-access.pdf

[clickman]

Reading now .. Good info I think ...

Looks like we have User Leel for sure ... I will let you know.

[clickman]

Reading now .. Good info I think ...

Looks like we have User Level at least for sure might be both ... I will let you know.

[clickman]

ChrisE
You are the man .. using your code as the starting point I was able to have success.. Thanks you so much for sending this to the forum.

[clickman]

Hello All .. I see that in Alter Table I can set the field type.

For example Text(20), Number, etc... but can one drill down deeper and set the items under what is shown on the "General" tab in the Access IDE.

For example set other attributes of that field.. For example one sets the Field to be a number type, but wants additionally to set it as a decimal with a precision of 6, and no indexing?

Looked at the info on Constraints but did not seem to apply.

[dilettante]

From Intermediate Microsoft Jet SQL for Access 2000:

With the new DECIMAL data type, you can also set the precision and scale of the number. The precision is the total number of digits that the field can contain, while the scale determines how many of those digits can be to the right of the decimal point. For the precision, the default is 18 and the maximum allowed value is 28. For the scale, the default is 0 and the maximum allowed value is 28.

CREATE TABLE tblDecimalDataTypes (
   DefaultType DECIMAL,
   SpecificType DECIMAL(10,5))

That article is in my October 2001 MSDN Library, the last edition that supports VB6. I'm not sure whether the article is still online at any Microsoft web site today. Here are a few more excerpts...

Precision Property:

Byte value that indicates the maximum number of digits used to represent values.

NumericScale Property:

Byte value that indicates the number of decimal places to which numeric values will be resolved.

[clickman]

https://blog.pagesd.info/public/2006...for-access.pdf

still online -- but i downloaded and added the paper to my library

Thanks so much