Please help with this code

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

The case:

I wrote the following code to do the following:

1) to import a sheet and range from excel
2) Name the imported table “AllGenes”
3) Using 3 append qrys, I take the appropriate data from
the “AllGenes” table to append 3 tables in the database
4) Then renaming the “AllGenes” to “AllGenes_Date_time”
ex. AllGenes_7-11-03 5:30 PM so that I can use
the “AllGenes” name again to keep the Append qrys working
without having to change the table name.

The Problem:

Step 4 does not work successfully every time and I always
get an error message at the end saying:
Errorr:(0)
I do not think that the error message has anything to do
with the failure of renaming the “AllGenes” table. Any
ideas?
The code:
Private Sub Command4_Click()
On Error GoTo ErrHandler
Dim Db As Database
Dim tdf As TableDef
Dim tblName As String
Dim intI As Integer
Dim intNumTbls As Integer

Set Db = OpenDatabase("GeneArrayMgr97.mdb")

Db.TableDefs.Refresh
intNumTbls = Db.TableDefs.Count

If IsNull(Me![DirName]) Or IsNull(Me!
[SheetRangeName]) _
Or IsNull(Me![NewTableName]) Or IsNull(Me!
[DrugName]) _
Or IsNull(Me![DrugConc]) Or IsNull(Me![RunNo]) Or
IsNull(Me![PrepDate]) Then
MsgBox "You can not leave any field blank!
Please fill appropriate fields"
Else
DoCmd.TransferSpreadsheet acImport, 8,
Me![NewTableName], Me![DirName], True, Me![SheetRangeName]


DoCmd.OpenQuery "qryPopulateFilterTable"

DoCmd.OpenQuery "qryPopulateFilterDetailsTable"

DoCmd.OpenQuery "qryPopulateCorrectionsTable"

For intI = 0 To intNumTbls - 1
Set tdf = Db.TableDefs(intI)
If tdf.Name = "AllGenes" Then
tdf.Name = tdf.Name & "_" & Format(Now
())
Else
End If
Next intI
End If
ErrHandler:
MsgBox "Error: " & Err.Description & " (" & Err.Number
& ")"
'Resume
End Sub
***************
Thanks
Al
 
Insert "Exit Sub" just before the "ErrHandler:" line. Your code as written
*always* goes through the error handler, even if there is no error.

--
Ken Snell
<MS ACCESS MVP>

The case:

I wrote the following code to do the following:

1) to import a sheet and range from excel
2) Name the imported table “AllGenes”
3) Using 3 append qrys, I take the appropriate data from
the “AllGenes” table to append 3 tables in the database
4) Then renaming the “AllGenes” to
“AllGenes_Date_time”
ex. AllGenes_7-11-03 5:30 PM so that I can use
the “AllGenes” name again to keep the Append qrys working
without having to change the table name.

The Problem:

Step 4 does not work successfully every time and I always
get an error message at the end saying:
Errorr:(0)
I do not think that the error message has anything to do
with the failure of renaming the “AllGenes” table. Any
ideas?
The code:
Private Sub Command4_Click()
On Error GoTo ErrHandler
Dim Db As Database
Dim tdf As TableDef
Dim tblName As String
Dim intI As Integer
Dim intNumTbls As Integer

Set Db = OpenDatabase("GeneArrayMgr97.mdb")

Db.TableDefs.Refresh
intNumTbls = Db.TableDefs.Count

If IsNull(Me![DirName]) Or IsNull(Me!
[SheetRangeName]) _
Or IsNull(Me![NewTableName]) Or IsNull(Me!
[DrugName]) _
Or IsNull(Me![DrugConc]) Or IsNull(Me![RunNo]) Or
IsNull(Me![PrepDate]) Then
MsgBox "You can not leave any field blank!
Please fill appropriate fields"
Else
DoCmd.TransferSpreadsheet acImport, 8,
Me![NewTableName], Me![DirName], True, Me![SheetRangeName]


DoCmd.OpenQuery "qryPopulateFilterTable"

DoCmd.OpenQuery "qryPopulateFilterDetailsTable"

DoCmd.OpenQuery "qryPopulateCorrectionsTable"

For intI = 0 To intNumTbls - 1
Set tdf = Db.TableDefs(intI)
If tdf.Name = "AllGenes" Then
tdf.Name = tdf.Name & "_" & Format(Now
())
Else
End If
Next intI
End If
ErrHandler:
MsgBox "Error: " & Err.Description & " (" & Err.Number
& ")"
'Resume
End Sub
***************
Thanks
Al
 
thank you very much for this tip. It took care of the
error message. Now the code functions correctely except
for the part after running the append qrys. It does not
rename the "AllGenes" table with adding date and time to
the table name. I placed a breakpoint and found out that
it goes through the loop and not recognize the table name.
here is that portion of the code:
********

For intI = 0 To intNumTbls - 1
Set tdf = Db.TableDefs(intI)
If tdf.Name = "AllGenes" Then
tdf.Name = tdf.Name & "_" & Format(Now
())
Else
End If
Next intI
End If
**********************
what am I missing here that is making it not finding the
allgenes table name and changing it.
thanks
Al
-----Original Message-----
Insert "Exit Sub" just before the "ErrHandler:" line. Your code as written
*always* goes through the error handler, even if there is no error.

--
Ken Snell
<MS ACCESS MVP>

The case:

I wrote the following code to do the following:

1) to import a sheet and range from excel
2) Name the imported table “AllGenes”
3) Using 3 append qrys, I take the appropriate data from
the “AllGenes” table to append 3 tables in the database
4) Then renaming the “AllGenes” to
“AllGenes_Date_time”
ex. AllGenes_7-11-03 5:30 PM so that I can use
the “AllGenes” name again to keep the Append qrys working
without having to change the table name.

The Problem:

Step 4 does not work successfully every time and I always
get an error message at the end saying:
Errorr:(0)
I do not think that the error message has anything to do
with the failure of renaming the “AllGenes” table. Any
ideas?
The code:
Private Sub Command4_Click()
On Error GoTo ErrHandler
Dim Db As Database
Dim tdf As TableDef
Dim tblName As String
Dim intI As Integer
Dim intNumTbls As Integer

Set Db = OpenDatabase("GeneArrayMgr97.mdb")

Db.TableDefs.Refresh
intNumTbls = Db.TableDefs.Count

If IsNull(Me![DirName]) Or IsNull(Me!
[SheetRangeName]) _
Or IsNull(Me![NewTableName]) Or IsNull(Me!
[DrugName]) _
Or IsNull(Me![DrugConc]) Or IsNull(Me![RunNo]) Or
IsNull(Me![PrepDate]) Then
MsgBox "You can not leave any field blank!
Please fill appropriate fields"
Else
DoCmd.TransferSpreadsheet acImport, 8,
Me![NewTableName], Me![DirName], True, Me![SheetRangeName]


DoCmd.OpenQuery "qryPopulateFilterTable"

DoCmd.OpenQuery "qryPopulateFilterDetailsTable"

DoCmd.OpenQuery "qryPopulateCorrectionsTable"

For intI = 0 To intNumTbls - 1
Set tdf = Db.TableDefs(intI)
If tdf.Name = "AllGenes" Then
tdf.Name = tdf.Name & "_" & Format(Now
())
Else
End If
Next intI
End If
ErrHandler:
MsgBox "Error: " & Err.Description & " (" & Err.Number
& ")"
'Resume
End Sub
***************
Thanks
Al


.
Click to expand...
 
What is the "name" that your code is showing for tdf.Name when the table is
"AllGenes"?

I tested your code and it works in my test. Are you sure that the name of
the table is "AllGenes"?
 
It is "AllGenes". I made the default in the text box
[NewTableName] to be "AllGenes" and this is where the,
TransferSpreadsheet acImport, line in the code is picking
the name from.
 
Glad it's working. Good luck.

Al said:
Dear Ken,
I thank you very much for your help. I used the following
function and called it from withing the procedure and it
has been working since. Here it is, take a look:

***************
Function IsTableQuery(DbName As String, TName As
String) As Integer
Dim Db As Database, Found As Integer, Test As
String
Const NAME_NOT_IN_COLLECTION = 3265

' Assume the table or query does not exist.
Found = False

' Trap for any errors.
On Error Resume Next

' If the database name is empty...
If Trim$(DbName) = "" Then
' ...then set Db to the current Db.
Set Db = CurrentDb()
Else
' Otherwise, set Db to the specified open
database.
Set Db = DBEngine.Workspaces(0).OpenDatabase
(DbName)

' See if an error occurred.
If Err Then
MsgBox "Could not find database to open: "
& DbName
IsTableQuery = False
Exit Function
End If
End If

' See if the name is in the Tables collection.
Test = Db.TableDefs(TName).Name
If Err <> NAME_NOT_IN_COLLECTION Then
Found = True
Db.TableDefs(TName).Name = Test & "_" & Format
(Now())
End If
' Reset the error variable.
Err = 0

' See if the name is in the Queries collection.
Test = Db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True

Db.Close

IsTableQuery = Found

End Function
**********************
thanks
Al
-----Original Message-----
Hmmm....if you can zip up the database (with just the parts that will show
the problem), email it to me (delete the
Click to expand...
words "this" "is" "not" "real" from
my return email address) and I'll take a look.

--
Ken Snell
<MS ACCESS MVP>

al said:
It is "AllGenes". I made the default in the text box
[NewTableName] to be "AllGenes" and this is where the,
TransferSpreadsheet acImport, line in the code is picking
the name from.
-----Original Message-----
What is the "name" that your code is showing for tdf.Name
when the table is
"AllGenes"?

I tested your code and it works in my test. Are you sure
that the name of
the table is "AllGenes"?

--
Ken Snell
<MS ACCESS MVP>

thank you very much for this tip. It took care of the
error message. Now the code functions correctely except
for the part after running the append qrys. It does not
rename the "AllGenes" table with adding date and time to
the table name. I placed a breakpoint and found out that
it goes through the loop and not recognize the table
name.
here is that portion of the code:
********

For intI = 0 To intNumTbls - 1
Set tdf = Db.TableDefs(intI)
If tdf.Name = "AllGenes" Then
tdf.Name = tdf.Name & "_" & Format
(Now
())
Else
End If
Next intI
End If
**********************
what am I missing here that is making it not finding the
allgenes table name and changing it.
thanks
Al


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top