Solved

Getting 3078 error when running some VB script

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

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now