Appending a field to a table -AND- accessing a split back-end data

[Michael]

My first question...
Why is it that the following line of code does not work with the
dbDecimal type, but it does work with dbText, dbMemo, dbDate, dbBoolean,
dbCurrency, dbLong, dbInteger, dbByte, dbSingle, and dbDouble? I listed all
of those because I can not imagine my syntax being wrong if only 1 out of 11
fail. I am using Access 2003 if it helps. What am I doing wrong? The code
I am using is:

With tdfTable
.Fields.Append .CreateField(strLabel, dbDecimal)
End With

I get an error that says that dbDecimal is an invalid type.

My second question is regarding linked tables...
I have a database that I used the splitter on. I am trying to
programatically create some querries in the back-end and also update some
records in other databases. I have no problem creating a database object for
the other databases using the following line:

Set dbNamed = OpenDatabase(strPath & strName, _
True)

When it gets to the back-end database I get an error saying that the
database is already opened exclusive by user admin. I figured that since the
tables were linked it might already be opened and be a part of the default
workspace. I tried looping through to see if it was there, but I do not see
it. How do I access the back-end database? The following is how I was
trying to find it:

Dim dbsLoop As Database
Dim wrkDefault As Workspace

For Each wrkDefault In Workspaces
For Each dbsLoop In wrkDefault.Databases
MsgBox dbsLoop.Name
Next dbsLoop
Next wrkDefault

Any help is appreciated!

Thank you,
Michael

 

[Allen Browne]

Question 1: Decimal data type
=======================
The Decimal data type is only partially implemented in Access.

DAO doesn't handle creating this type.

You cannot create it with a DDL statement executed under DAO either.

CreateField() takes only one argument for Size, so even if you could create
it, you could not specify the Precision and Scale.

There is no Decimal data type in VBA either, so you are stuck with using a
variant subtyped as a decimal.

And even if you could solve all those issues (or work around them using
ADO), JET still can't even perform a basic sort on this type of field:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

I actually find it very embarassing that JET can get such a basic query
wrong. Even more so because Microsoft is quite happy leaving it that broken
for more than 5 years. I advise you not to use this data type if you have
any other choice avaiable.

Question 2: Other databases
======================
Explicitly close everything you open, and set your object to Nothing. That
should get you out of trouble.

If you are trying to figure out who is connected to a database, see:
How to determine who is logged on to a database by using
Microsoft Jet UserRoster in Access 2000
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;198755

In some cases, you can manipulate data in other databases without having to
connect like that. For example, when you create an Append or Make Table
query, the dialog offers you the choice of "In another database". If you do
that, you will see an IN phrase in the SQL statement, e.g.:
IN 'C:\MyFolder\MyFile.mdb"

 

[Michael]

That completely answers my first question. I thought that I could append
precision and scale as properties to the field, but since I can not create a
field with the data type decimal programatically, that ends before it begins.

Thank you Allen.

I do not think that I conveyed the second part well...

I am working in the front end of a split database and I want to modify the
back-end by adding tables and querries progamatically. If I declare a
database variable and try to set it to the OpenDatabase() method for my
back-end DB I get an error stating that the back-end is already open
exclusively by admin. I did not open the database programatically or
otherwise at this point, except that the tables in the front-end(which I am
working in) are linked tables who's source is in the back-end, and fields
from those tables are used on my front-end form.

I want to create a variable that references my back-end, or use whatever
access is using(since it seems that access is the one who is opening the DB
exclusively). I would have assumed that it would show up in Workspaces, but
I can not find it there either. There is nothing for me to dispose of,
because I have not managed to create it yet.

If you were to create a database(Test.mdb) with a table(tblTable) in it that
had a field (fldField) and then you were to create a form(frmForm) with
tblTable as it's record source and add fldField to it and place a command
button on it. Then use the Database Splitter under Tools->Database Utilities
to create a back-end(Test_be.mdb), which your table(tblExample) would now be
in. Now if you wanted to add code to the command button on frmForm in your
front-end that added another table(tblNew) in your back-end. What would the
code be for the command button. Assume both database are on c:\

Thanks in advance,
Michael

 

[Michael]

The other thing that I noticed is that the database is open exclusively by
admin, but there is no .ldb file for the back-end at the time I get the
message.

 

[Allen Browne]

Someone else may have a better answer for this 2nd question than I.
Hopefully they will contribute.

IME, you are very likely to run into this problem if the linked tables have
been used at all, and that's almost always the case in an fe/be arrangement.
I guess you could try deleting all the linked tables, waiting for the
timeout period (however long that is on the operating system), then seeing
if you can get an exclusive lock, running your code, and then creating the
links again.

What I prefer to do is to create a separate "updater" mdb to perform the
updates. It has no linked tables, and asks the user for the location of the
back end file. It will then OpenDatabase exclusively, and perform the
changes, logging each step, and showing a report of the errors if anything
did not complete as expected.

I am assuming the goal here is to update a back end database to a later
version at a remote site where you cannot perform the changes yourself.

Not sure if Tony Toews' article will help:
http://www.granite.ab.ca/access/backendupdate.htm

 

[John Spencer]

In my experience, you have to close any objects that use the backend
database. So if you have any open forms, reports, or queries that use
tables in the backend, you must close them. (Aside: I once had a problem
with this because I forgot about a hidden form that automatically opened in
the database. I found it was open by looking at the forms collection.)

Once I have made sure all the objects are closed, then my code to update the
backend database runs.

 

[Michael]

Thanks again Allen.

Your link "Tony Toew's article" was also helpful.

Regards,
Michael

 

[Michael]

Thanks John,

Somehow I have managed to avoid this situation in the past, but it
sound like what I am doing is technically correct, I just have to modify
where I am doing it.

Thanks,
Michael