stellyuk
asked on
Getting 3078 error when running some VB script
Hi there,
I'm getting a 3078 run time error, it cant fing the TMP table when running the below script in VB... I dont know how to sort this one out...
Private Sub Command0_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you Sure You Want to Cockpit Kit Data?"
Style = vbYesNo + vbInformation + vbDefaultButton2
Title = "Data Import"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
DoCmd.SetWarnings (WarningsOff)
Dim deletecpk As String
Dim tmpTbl_Insert As String
Dim tmpTbl_Delete As String
Dim tmpTbl_Copy As String
Dim db As DAO.Database
Set db = CurrentDb()
Dim con As New ADODB.Connection
deletecpk = "DELETE * FROM TBL_COCKPIT_KIT"
DoCmd.RunSQL deletecpk
con.Open _
"Provider=Microsoft.ACE.OL
tmpTbl_Insert = "SELECT * INTO TBL_COCKPIT_KIT_TMP FROM [COCKPIT$] IN ""S:\0005_Production_Backu
con.Execute tmpTbl_Insert
con.Close
Set con = Nothing
db.TableDefs.Refresh
Application.RefreshDatabas
tmpTbl_Copy = "INSERT INTO TBL_COCKPIT_KIT SELECT * FROM TBL_COCKPIT_KIT_TMP"
DoCmd.RunSQL tmpTbl_Copy
tmpTbl_Delete = "DROP TABLE TBL_COCKPIT_KIT_TMP"
DoCmd.RunSQL tmpTbl_Delete
Else: End
End If
End Sub
All help is greatly recieved...
Stelly
I'm getting a 3078 run time error, it cant fing the TMP table when running the below script in VB... I dont know how to sort this one out...
Private Sub Command0_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you Sure You Want to Cockpit Kit Data?"
Style = vbYesNo + vbInformation + vbDefaultButton2
Title = "Data Import"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
DoCmd.SetWarnings (WarningsOff)
Dim deletecpk As String
Dim tmpTbl_Insert As String
Dim tmpTbl_Delete As String
Dim tmpTbl_Copy As String
Dim db As DAO.Database
Set db = CurrentDb()
Dim con As New ADODB.Connection
deletecpk = "DELETE * FROM TBL_COCKPIT_KIT"
DoCmd.RunSQL deletecpk
con.Open _
"Provider=Microsoft.ACE.OL
tmpTbl_Insert = "SELECT * INTO TBL_COCKPIT_KIT_TMP FROM [COCKPIT$] IN ""S:\0005_Production_Backu
con.Execute tmpTbl_Insert
con.Close
Set con = Nothing
db.TableDefs.Refresh
Application.RefreshDatabas
tmpTbl_Copy = "INSERT INTO TBL_COCKPIT_KIT SELECT * FROM TBL_COCKPIT_KIT_TMP"
DoCmd.RunSQL tmpTbl_Copy
tmpTbl_Delete = "DROP TABLE TBL_COCKPIT_KIT_TMP"
DoCmd.RunSQL tmpTbl_Delete
Else: End
End If
End Sub
All help is greatly recieved...
Stelly
According to the Access 2007 help, the syntax for SELECT INTO is:
SELECT field1[, field2[, &]] INTO newtable [IN externaldatabase] FROM source
The SELECT&INTO statement has these parts:
Part--Description
field1, field2--The name of the fields to be copied into the new table.
newtable--The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase--The path to an external database. For a description of the path, see the IN clause.
source--The name of the existing table from which records are selected. This can be single or multiple tables or a query.
And the syntax for the IN clause for an Excel worksheet is:
FROM [Customers$] IN "c:\documents\xldata.xls" "EXCEL 5.0;"
So, I think your SELECT INTO statement should be:
tmpTbl_Insert = "SELECT * INTO TBL_COCKPIT_KIT_TMP IN ""S:\0005_Production_Backu
SELECT field1[, field2[, &]] INTO newtable [IN externaldatabase] FROM source
The SELECT&INTO statement has these parts:
Part--Description
field1, field2--The name of the fields to be copied into the new table.
newtable--The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase--The path to an external database. For a description of the path, see the IN clause.
source--The name of the existing table from which records are selected. This can be single or multiple tables or a query.
And the syntax for the IN clause for an Excel worksheet is:
FROM [Customers$] IN "c:\documents\xldata.xls" "EXCEL 5.0;"
So, I think your SELECT INTO statement should be:
tmpTbl_Insert = "SELECT * INTO TBL_COCKPIT_KIT_TMP IN ""S:\0005_Production_Backu
stellyuk,
the "Select into " statement is for creating an Access table in an Access db application
are you trying to export a table to an Excel file?
if so, you will need something
docmd.TransferSpreadsheet acExport,acSpreadsheetType
the "Select into " statement is for creating an Access table in an Access db application
are you trying to export a table to an Excel file?
if so, you will need something
docmd.TransferSpreadsheet acExport,acSpreadsheetType
ASKER
I'm trying to import an excel spreadsheet into Access... but when I create the temporary table I dont hink access can see it, it does not display in the list of tables and I think that is why it can't find it.
When I open and close Access or refresh it with F5 the temporary table is there with data in it as well... any ideas?
Stelly
When I open and close Access or refresh it with F5 the temporary table is there with data in it as well... any ideas?
Stelly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,
Stelly