Solved

Getting 3078 error when running some VB script

Posted on 2008-10-10
5
765 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

734 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