Solved

Getting 3078 error when running some VB script

Posted on 2008-10-10
5
763 Views
Last Modified: 2013-11-28
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.OLEDB.12.0;Data Source=S:\0005_Production_Backup_Database\Production_Backup_Database.accdb;Persist Security Info=False;"
   tmpTbl_Insert = "SELECT * INTO TBL_COCKPIT_KIT_TMP FROM [COCKPIT$] IN ""S:\0005_Production_Backup_Database\GEPICS_EXPORT_FILES\COCKPIT_KIT\COCKPIT_ONE.xls"" ""Excel 8.0; HDR=No;"""

con.Execute tmpTbl_Insert

con.Close
Set con = Nothing

db.TableDefs.Refresh
Application.RefreshDatabaseWindow

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
0
Comment
Question by:stellyuk
  • 2
  • 2
5 Comments
 

Author Comment

by:stellyuk
ID: 22685284
Can anyone please help??

Thanks,

Stelly
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22686397
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_Backup_Database\GEPICS_EXPORT_FILES\COCKPIT_KIT\COCKPIT_ONE.xls"" ""EXCEL 8.0;"" FROM [COCKPIT$]"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22687048
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,acSpreadsheetTypeExcel9,"tableName","S:\0005_Production_Backup_Database\GEPICS_EXPORT_FILES\COCKPIT_KIT\COCKPIT_ONE.xls"
0
 

Author Comment

by:stellyuk
ID: 22700555
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22701599
to import excel file to access

docmd.transferspreadsheet acimport,acSpreadsheetTypeExcel9, _
      "Temptable", "S:\0005_Production_Backup_Database\GEPICS_EXPORT_FILES\COCKPIT_KIT\COCKPIT_ONE.xls", True, "Sheet1!"

will import all the contents of Sheet1
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question