Solved

Getting 3078 error when running some VB script

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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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