Can't get around temp table in front-end

[Robert McEuen]

Using Access 97 on WinXP

I have data in a DB2 table that I'm trying to get into an identical
table in my backend db. Based on volume of data and frequency of
download, I'm trying to avoid having a temp table created in my
frontend.

I downloaded the temp database code from Tony Toews's site, and
altered it to fit my needs (I thought), and it functions properly all
the way to the point where the data is inserted into a new table (see
below). At that point, rather than inserting into the table created
in the temp database, a new table by the same name with the number 1
appended is created on my frontend.

Can someone please take a look and tell me where I'm going wrong? I
feel I'm very close, but can't quite get over the hump.

Many thanks in advance!

'****CODE START****
Sub TempTable(strTableName As String)
On Error GoTo tagError

Dim db As Database
Dim tdf As TableDef
Dim dbTemp As Database
Dim tdfTemp As TableDef
Dim RS As Recordset
Dim wrkDefault As Workspace
Dim strTempDatabase As String
Dim strTempTableName As String
Dim fld As Field

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)

strTempDatabase = Left$(db.Name, Len(db.Name) - 4) & " temp.mdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

' Create a new temp database
Set dbTemp = wrkDefault.CreateDatabase(strTempDatabase,
dbLangGeneral)

strTempTableName = "temp_" & strTableName

' Delete the link to the temp table if it exists
' (separate sub, also from Tony Toews)
If TableExists(strTempTableName) Then
db.TableDefs.Delete strTempTableName
End If

' Create the temp table
Set tdfTemp = dbTemp.CreateTableDef(strTempTableName)

' Create fields in the temp table identical to those of the
original
For Each fld In tdf.Fields
tdfTemp.Fields.Append tdfTemp.CreateField(fld.Name, fld.Type,
fld.Size)
Next

dbTemp.TableDefs.Append tdfTemp

dbTemp.TableDefs.Refresh

Dim tdfLinked As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked = db.CreateTableDef(strTempTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTempTableName
db.TableDefs.Append tdfLinked

db.TableDefs.Refresh

RefreshDatabaseWindow

' Clear records from destination table
DoCmd.RunSQL ("DELETE * FROM " & strTableName & ";")

' Import table from DB2.
' OConnect() is a separate function that creates
' the connection string.
' >>>This is where the FE table with "1" appended is
' being created:
DoCmd.TransferDatabase acImport, "ODBC Database", OConnect(),
acTable, "MYSYSID.MYTABLE", strTempTableName, False

' Append to back-end
Dim strSQL As String
Dim qdf As QueryDef
Dim nRecAff As Long

strSQL = "INSERT INTO " & strTableName & " SELECT " &
strTempTableName & ".* FROM [" & strTempTableName & "];"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute dbFailOnError
nRecAff = qdf.RecordsAffected

' Separate sub to record timestamp, table affected, number
recs
Call UpdateTS(strTableName, nRecAff)
dbTemp.Close

db.TableDefs.Refresh

Set qdf = Nothing
Set dbTemp = Nothing

' Unlink the tables
db.TableDefs.Delete strTempTableName

' Delete the temp mdb
strTempDatabase = Left$(db.Name, Len(db.Name) - 4) & " temp.mdb"
Kill (strTempDatabase)
tagExit:

Exit Sub

tagError:
DoCmd.Hourglass False
If Err.Number = 70 Then
MsgBox "Unable to delete temporary database as it is locked."
& vbCrLf & vbCrLf & _
"Import cancelled."
Else
MsgBox Err.Description, vbCritical
End If

End Sub
'****CODE END****

[Doug Hutcheson]

"Robert McEuen" <UN**********@yahoo.com> wrote in message
news:fb*************************@posting.google.co m...

Using Access 97 on WinXP

I have data in a DB2 table that I'm trying to get into an identical
table in my backend db. Based on volume of data and frequency of
download, I'm trying to avoid having a temp table created in my
frontend.

I downloaded the temp database code from Tony Toews's site, and
altered it to fit my needs (I thought), and it functions properly all
the way to the point where the data is inserted into a new table (see
below). At that point, rather than inserting into the table created
in the temp database, a new table by the same name with the number 1
appended is created on my frontend.

Can someone please take a look and tell me where I'm going wrong? I
feel I'm very close, but can't quite get over the hump.

Many thanks in advance!

'****CODE START****
Sub TempTable(strTableName As String)
On Error GoTo tagError

Dim db As Database
Dim tdf As TableDef
Dim dbTemp As Database
Dim tdfTemp As TableDef
Dim RS As Recordset
Dim wrkDefault As Workspace
Dim strTempDatabase As String
Dim strTempTableName As String
Dim fld As Field

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)

strTempDatabase = Left$(db.Name, Len(db.Name) - 4) & " temp.mdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

' Create a new temp database
Set dbTemp = wrkDefault.CreateDatabase(strTempDatabase,
dbLangGeneral)

strTempTableName = "temp_" & strTableName

' Delete the link to the temp table if it exists
' (separate sub, also from Tony Toews)
If TableExists(strTempTableName) Then
db.TableDefs.Delete strTempTableName
End If

' Create the temp table
Set tdfTemp = dbTemp.CreateTableDef(strTempTableName)

' Create fields in the temp table identical to those of the
original
For Each fld In tdf.Fields
tdfTemp.Fields.Append tdfTemp.CreateField(fld.Name, fld.Type,
fld.Size)
Next

dbTemp.TableDefs.Append tdfTemp

dbTemp.TableDefs.Refresh

Dim tdfLinked As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked = db.CreateTableDef(strTempTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTempTableName
db.TableDefs.Append tdfLinked

db.TableDefs.Refresh

RefreshDatabaseWindow

' Clear records from destination table
DoCmd.RunSQL ("DELETE * FROM " & strTableName & ";")

' Import table from DB2.
' OConnect() is a separate function that creates
' the connection string.
' >>>This is where the FE table with "1" appended is
' being created:
DoCmd.TransferDatabase acImport, "ODBC Database", OConnect(),
acTable, "MYSYSID.MYTABLE", strTempTableName, False

' Append to back-end
Dim strSQL As String
Dim qdf As QueryDef
Dim nRecAff As Long

strSQL = "INSERT INTO " & strTableName & " SELECT " &
strTempTableName & ".* FROM [" & strTempTableName & "];"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute dbFailOnError
nRecAff = qdf.RecordsAffected

' Separate sub to record timestamp, table affected, number
recs
Call UpdateTS(strTableName, nRecAff)
dbTemp.Close

db.TableDefs.Refresh

Set qdf = Nothing
Set dbTemp = Nothing

' Unlink the tables
db.TableDefs.Delete strTempTableName

' Delete the temp mdb
strTempDatabase = Left$(db.Name, Len(db.Name) - 4) & " temp.mdb"
Kill (strTempDatabase)
tagExit:

Exit Sub

tagError:
DoCmd.Hourglass False
If Err.Number = 70 Then
MsgBox "Unable to delete temporary database as it is locked."
& vbCrLf & vbCrLf & _
"Import cancelled."
Else
MsgBox Err.Description, vbCritical
End If

End Sub
'****CODE END****

Robert,
You are specifically using CurrentDb, which is your local (front-end)
instance.
You need to create a new Workspace object and open the back-end Access
database in it, then perform your table creation in that workspace, if you
follow my drift.
HTH
Doug Hutcheson

--
Remove the blots from my address to reply