Solved

Getting 3078 error when running some VB script

Posted on 2008-10-10
5
767 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

626 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