|
 |
access thread: [Access] Updating Tables Collection
Message #1 by "Gerald, Rand" <RGerald@u...> on Wed, 30 Oct 2002 18:25:13 -0600
|
|
Dear Friends,
This one may be a challenge!
I am using a Microsoft routine (See: Knowledge Base #Q219598) to check
if a
table exists and if it does not, to create it with a SQL command. See
Code
below.
The new table appears to be created - I see it in the tables list and I
can
open it from the tables list. However, it doesn't seem to be included
in
the tables collection. If I rerun the code below again,
DoesObjectExist()
returns False as if the table is not there. If on the other hand, I
close
the database and re-open it, the code runs as expected.
How can I cause the new table to be found in the tables collection on
subsequent passes through this code?
' ---------- Begin Code -------------
If IsNull(strTableToEdit) Or Len(Trim(Nz(strTableToEdit, ""))) =3D
0 Then
MsgBox "Blank Table Name" & vbCrLf & "Canceling Edit!",
vbCritical
Exit Sub
Else
If Not DoesObjectExist("Tables", strTableToEdit) Then
If vbYes =3D MsgBox("Table: " & strTableToEdit & " NOT
Found" &
vbCrLf & "Do you wish to create it?", vbInformation + vbYesNo, "Missing
Table") Then
strCreateTable =3D "CREATE TABLE " & strTableToEdit & "
(BLC
VARCHAR(6) PRIMARY KEY);"
On Error Resume Next ' <<<< --- This isn't
proactive. I'd like to get rid of it.
' It keeps the program from erroring out when it tries to create an
existing
table.
DoCmd.RunSQL strCreateTable ' <<<< --- New Table
Created
here
On Error GoTo Err_cmdEditDataSet_Click ' <<<< ----
Back to
normal. This should go also.
Else
Exit Sub
End If
End If
End If
strDataSetSQL =3D "SELECT " & strTableToEdit & ".BLC,
dbo_Agency.BLC_NAME,
Left([" & strTableToEdit & "]![BLC],2) AS STATE, dbo_AGENCY.COMM_TYPE,
dbo_AGENCY.ADDR_STAT FROM " & strTableToEdit & " LEFT JOIN dbo_AGENCY
ON " &
strTableToEdit & ".BLC =3D dbo_AGENCY.BLC ORDER BY State,
dbo_Agency.BLC_NAME;"
' ------------ End Code -------------------
After the above code runs, I open a form with strDataSetSQL as the
RecordSource.
' ------------ Microsoft Code ------------
Function DoesObjectExist(ObjectType$, ObjectName$)
On Error Resume Next
Dim Found_Object, Find_Object As String, ObjectNum As Integer
Dim DB As Database, T As TableDef
Dim Q As QueryDef, C As Container
Dim Msg As String
Found_Object =3D -1
Set DB =3D DBEngine(0)(0)
Select Case ObjectType$
Case "Tables"
Find_Object =3D DB.TableDefs(ObjectName$).name
Case "Queries"
Find_Object =3D DB.QueryDefs(ObjectName$).name
Case Else
If ObjectType$ =3D "Forms" Then
ObjectNum =3D 1
ElseIf ObjectType$ =3D "Modules" Then
ObjectNum =3D 2
ElseIf ObjectType$ =3D "Reports" Then
ObjectNum =3D 4
ElseIf ObjectType$ =3D "Macros" Then
ObjectNum =3D 5
Else
Msg =3D "Object Name """ & ObjectType & """ is an invalid"
Msg =3D Msg & " argument to function ObjectExists_20!"
MsgBox Msg, 16, "ObjectExists_20"
Exit Function
End If
Set C =3D DB.Containers(ObjectNum)
Find_Object =3D C.Documents(ObjectName$).name
End Select
If Err =3D 3265 Or Find_Object =3D "" Then
Found_Object =3D 0
End If
DoesObjectExist =3D Found_Object
End Function
' ------------ End Microsoft Code -----
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Wednesday, October 30, 2002 16:44
To: Access
Subject: [access] RE: Error Message after Compacting
Hi Dave,
Why are you copying the files before compacting?
Simply remove everyone from the database and the LDB file will be
deleted
immediately.
You may be copying the database to another location which has different
folder permissions, therefore denying read/write access and causign the
error.
Cheers,
Andrew
-----Original Message-----
From: Dave Baker [mailto:wimblebury@h...]
Sent: Wednesday, 30 October 2002 8:07 PM
To: Access
Subject: [access] Error Message after Compacting
I have an Access database operating in a multiuser LAN environment.
To compact the database I copy the mdb and ldb files to a seperate
location, first of all I open the database and then close it to delete
the
ldb file and then run the compact option from the toolbar.
I have recently started receiving the following message at the end of
the
compacting procedure:
'RECORD(S) CAN'T BE READ; NO READ PERMISSION ON C:\SMART.MDB'
Whilst the database appears to work fine after this, I do not like
getting
this new message.
Please help.
Dave
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.
Message #2 by Omar Chaudry <OChaudry@b...> on Thu, 31 Oct 2002 09:31:53 -0000
|
|
Are you appending the new table to the currentdb.tabledefs collection
and
then refreshing the collection after creating it?
Just a thought!
Omar
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: 31 October 2002 00:25
To: Access
Subject: [access] [Access] Updating Tables Collection
Importance: High
Dear Friends,
This one may be a challenge!
I am using a Microsoft routine (See: Knowledge Base #Q219598) to check
if a
table exists and if it does not, to create it with a SQL command. See
Code
below.
The new table appears to be created - I see it in the tables list and I
can
open it from the tables list. However, it doesn't seem to be included
in
the tables collection. If I rerun the code below again,
DoesObjectExist()
returns False as if the table is not there. If on the other hand, I
close
the database and re-open it, the code runs as expected.
How can I cause the new table to be found in the tables collection on
subsequent passes through this code?
' ---------- Begin Code -------------
If IsNull(strTableToEdit) Or Len(Trim(Nz(strTableToEdit, ""))) =3D
0 Then
MsgBox "Blank Table Name" & vbCrLf & "Canceling Edit!",
vbCritical
Exit Sub
Else
If Not DoesObjectExist("Tables", strTableToEdit) Then
If vbYes =3D MsgBox("Table: " & strTableToEdit & " NOT
Found" &
vbCrLf & "Do you wish to create it?", vbInformation + vbYesNo, "Missing
Table") Then
strCreateTable =3D "CREATE TABLE " & strTableToEdit & "
(BLC
VARCHAR(6) PRIMARY KEY);"
On Error Resume Next ' <<<< --- This isn't
proactive. I'd like to get rid of it.
' It keeps the program from erroring out when it tries to create an
existing
table.
DoCmd.RunSQL strCreateTable ' <<<< --- New Table
Created
here
On Error GoTo Err_cmdEditDataSet_Click ' <<<< ----
Back to
normal. This should go also.
Else
Exit Sub
End If
End If
End If
strDataSetSQL =3D "SELECT " & strTableToEdit & ".BLC,
dbo_Agency.BLC_NAME,
Left([" & strTableToEdit & "]![BLC],2) AS STATE, dbo_AGENCY.COMM_TYPE,
dbo_AGENCY.ADDR_STAT FROM " & strTableToEdit & " LEFT JOIN dbo_AGENCY
ON " &
strTableToEdit & ".BLC =3D dbo_AGENCY.BLC ORDER BY State,
dbo_Agency.BLC_NAME;"
' ------------ End Code -------------------
After the above code runs, I open a form with strDataSetSQL as the
RecordSource.
' ------------ Microsoft Code ------------
Function DoesObjectExist(ObjectType$, ObjectName$)
On Error Resume Next
Dim Found_Object, Find_Object As String, ObjectNum As Integer
Dim DB As Database, T As TableDef
Dim Q As QueryDef, C As Container
Dim Msg As String
Found_Object =3D -1
Set DB =3D DBEngine(0)(0)
Select Case ObjectType$
Case "Tables"
Find_Object =3D DB.TableDefs(ObjectName$).name
Case "Queries"
Find_Object =3D DB.QueryDefs(ObjectName$).name
Case Else
If ObjectType$ =3D "Forms" Then
ObjectNum =3D 1
ElseIf ObjectType$ =3D "Modules" Then
ObjectNum =3D 2
ElseIf ObjectType$ =3D "Reports" Then
ObjectNum =3D 4
ElseIf ObjectType$ =3D "Macros" Then
ObjectNum =3D 5
Else
Msg =3D "Object Name """ & ObjectType & """ is an invalid"
Msg =3D Msg & " argument to function ObjectExists_20!"
MsgBox Msg, 16, "ObjectExists_20"
Exit Function
End If
Set C =3D DB.Containers(ObjectNum)
Find_Object =3D C.Documents(ObjectName$).name
End Select
If Err =3D 3265 Or Find_Object =3D "" Then
Found_Object =3D 0
End If
DoesObjectExist =3D Found_Object
End Function
' ------------ End Microsoft Code -----
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Wednesday, October 30, 2002 16:44
To: Access
Subject: [access] RE: Error Message after Compacting
Hi Dave,
Why are you copying the files before compacting?
Simply remove everyone from the database and the LDB file will be
deleted
immediately.
You may be copying the database to another location which has different
folder permissions, therefore denying read/write access and causign the
error.
Cheers,
Andrew
-----Original Message-----
From: Dave Baker [mailto:wimblebury@h...]
Sent: Wednesday, 30 October 2002 8:07 PM
To: Access
Subject: [access] Error Message after Compacting
I have an Access database operating in a multiuser LAN environment.
To compact the database I copy the mdb and ldb files to a seperate
location, first of all I open the database and then close it to delete
the
ldb file and then run the compact option from the toolbar.
I have recently started receiving the following message at the end of
the
compacting procedure:
'RECORD(S) CAN'T BE READ; NO READ PERMISSION ON C:\SMART.MDB'
Whilst the database appears to work fine after this, I do not like
getting
this new message.
Please help.
Dave
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.
DISCLAIMER: The information in this message is confidential and may
be
legally privileged. It is intended solely for the addressee. Access to
this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any
action or omission taken by you in reliance on it, is prohibited and
may be
unlawful. Please immediately contact the sender if you have received
this
message in error. Thank you.
|
|
|
View Cart
