Re: Appending a field to a table -AND- accessing a split back-end data
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Tue, 13 Dec 2005 14:07:40 +0800
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"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Michael" <Michael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3649F561-5F91-4ACD-8102-14AECF649AEB@xxxxxxxxxxxxxxxx
> 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
.
- Prev by Date: Re: GUID
- Next by Date: Re: Crosstab Situation
- Previous by thread: Re: Enable/disable controls in subform
- Next by thread: Re: Appending a field to a table -AND- accessing a split back-end
- Index(es):
Relevant Pages
|
