473,238 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

Can't get around temp table in front-end

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****
Nov 13 '05 #1
1 2864
"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
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Per | last post by:
Why cant I use the same temptable name i a stored procedure after i have droped it? I use the Pubs database for the test case. CREATE PROCEDURE spFulltUttrekk AS SELECT * INTO #temp FROM...
5
by: Jay Chan | last post by:
I am trying to use a command line program to run a stored procedure that generates output in a comma-delimitted format. Somehow, ISQL or OSQL always wrap the lines at 256 characters. I believe this...
17
by: Jon Ole Hedne | last post by:
I have worked on this problem some hours now (read many-many...), and I can't solve it: In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the...
16
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number...
6
by: MLH | last post by:
I was able to do this from Access 2.0. It had to be set up, of course, but it could be done. I'm unsure as to why Access 97 says "Can't find file"??? ...
5
by: wackyphill | last post by:
If you were doing paging of results on a web page and were interested in grabbing say records 10-20 of a result set. But also wanted to know the total # of records in the result set (so you could...
10
by: Extremest | last post by:
I know there are ways to make this a lot faster. Any newsreader does this in seconds. I don't know how they do it and I am very new to c#. If anyone knows a faster way please let me know. All...
1
by: imani_technology_spam | last post by:
Right now, a client of mine has a T-SQL statement that does the following: 1) Create a temp table. 2) Populate temp table with data from one table using an INSERT statement. 3) Populate temp...
1
Eclipse
by: Eclipse | last post by:
G'day all Can anyone explain the difference in the results to me as I don't understand why specifying the directory name in two different ways could give a different answer. In CODE 1 below i...
2
by: everly | last post by:
Hi, I'm helping my friends band and making their MySpace. I messed something up and I can't figure out what it is. Everything is all out of whack on the bottom half of the page. The comments...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.