Modules & VBA :: Error 3274 - External Table Is Not In Expected Format
Apr 2, 2014
I have a function that exports a number of tables within an access 2007 database to one spreadsheet using docmd.transferspreadsheet.
The error message in the title of this post presents itself during a loop which transfers the tables to the spreadsheet. However, it doesn't always happen, sometimes it completes the process perfectly which is quite frustrating.
Below is the section of code where the error occurs ....
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Table_Exports WHERE Type = 'CRM'", dbOpenSnapshot)
rs.MoveFirst
Do Until rs.EOF
TableName = rs.Fields("Table")
WSName = rs.Fields("WSName")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, NewWBPath, True, WSName
rs.MoveNext
Loop
I'm currently using the following VBA to export all tables within my DB to Excel on separate tabs:
Dim td As DAO.TableDef, db As DAO.Database Dim out_file As String out_file = CurrentProject.Path & "" & "Backup.xls" Set db = CurrentDb() For Each td In db.TableDefs If Left(td.Name, 4) = "MSys" Then 'We do not need MSys tables in excel file Else DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames End If Next
But upon exporting I get the following error:
"Run-time error 3274' External table is not in expected format"
A lady at work created an Access database that imports a dBase 5 file and when I go to import this file on User #1 PC it gives the error;
"External table is not in the expected format."
I can use the same Access database and same dBase file to import (stored on a shared network drive) from User #2 & #3's PC's and all works well. The only problem is that it is User #1 job to do this.
I unloaded Access 2003 and reloaded it and still the same results. All 3 PC's are identical via hardware and software.
It use to work until this past Tuesday when it started to give the error.
My steps are: open the "Sales" Access database select the prices table Get External Data / Import and then select Prices.dbf Then the error message comes up.
I am not an Access database guy and could use some help!
I have a table onto which i would like to add new column based on a set of if statements. (to assign weekly dates)this is only a portion of the code, but when I clicked on run, it gives me "external name not defined error".
Code:
Option Compare Database Option Explicit Public Sub AssignWeekly() 'declares weekly column as a new variable Dim Client_Weekly_1213 As Integer
I am setting up a form to send multiple attachments to an email.I have a query "qry_DwgEmail" that has an ID and a path column. On the form I have a button with the code below in the Click Event.The code below all works fine unless the path is incorrect, so I would like some error handling to be able to either:
1) Stop the code from running and display a message box with my message. 2) Continue the loop and then have the message box display what files could not be found (Preferred).
Note, the following code was taken from one of the forums I am not 100% on what it all means.
Code: Private Sub btn_CreateEmail_Click() Dim MyDB As Database Dim MyRS As Recordset Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookAttach As Outlook.Attachment Dim TheAttachment As String
Hi All,I am getting problem "Error: too few parameters. Expected 1" when following Query is executed to updated a Flag Value in a table on Click event of a Submit button. CurrentDb.Execute "UPDATE Scheduled_Appointment SET Is_Taken = 1 WHERE Scheduled_Appointment_ID LIKE Me.Sch_P_ID"Where:Table: Scheduled_AppointmentColumn: Scheduled_Appointment_ID [Primary Key]Column: Is_Taken [ColumnType = Number ]Text Field: Me.Sch_P_ID [contains the Scheduled_Appointment_ID value for the selected Record on the Form]Thanks in Advance.
I have a error I need help with. Here is my code for my Global, and My Form Current: See the red for the Error Line, that show up in the VBE and the Blue is the code related to that line. I am using access 2003
'Declare all variables for right-side record counter Dim bdg As DAO.Recordset Dim swr As DAO.Recordset Dim wtr As DAO.Recordset Dim dmo As DAO.Recordset ''Dim dvt As DAO.Recordset Dim occ As DAO.Recordset Dim fre As DAO.Recordset Dim swrlat As DAO.Recordset Dim wrtlat As DAO.Recordset
Dim bdgCount As Integer Dim swrcount As Integer Dim wtrcount As Integer Dim dmocount As Integer Dim dvtcount As Integer Dim occcount As Integer Dim frecount As Integer Dim countswr As Integer 'laterial counter Dim countwtr As Integer 'laterial counter
Dim sqlbdg As String Dim sqlswr As String Dim sqlwtr As String Dim sqldmo As String ''Dim sqldvt As String Dim sqlocc As String Dim sqlfre As String Dim sqlswrlat As String Dim sqlwtrlat As String
Dim db As DAO.Database
Private Sub Form_Current() Set db = CurrentDb() 'Use SQL strings to pull data from the tables sqlbdg = "SELECT [Building].[PIN] FROM Building WHERE [Building].[PIN]='" & Me![ADDRESS3] & "' ;" sqlswr = "SELECT [Sewerform].[PIN] FROM [SEWER SERVICE LATERALS] WHERE [Sewerform].[PIN]='" & Me![ADDRESS3] & "' ;" sqlwtr = "SELECT [water].[PIN] FROM [WATER SERVICE LATERALS] WHERE [water].[PIN]='" & Me![ADDRESS3] & "' ;" sqlswrlat = "SELECT [SewerMain].[PIN] FROM [SEWER MAIN PRBLEMS] WHERE [SewerMain].[PIN]='" & Me![ADDRESS3] & "' ;" sqlwtrlat = "SELECT [WaterMain].[PIN] FROM [WATER MAIN PROBLEMS] WHERE [WaterMain].[PIN]='" & Me![ADDRESS3] & "' ;" sqldmo = "SELECT [Demolition Permits].[PID] FROM [Demolition Permits] WHERE [Demolition Permits].[PID]='" & Me![ADDRESS3] & "' ;" ''There is no PIN field in the development table ==> sqlwtr = "SELECT [Development Permits].[PIN] FROM [Development Permits] WHERE [Development Permits].[PIN]='" & Me![ADDRESS3] & "' ;" sqlocc = "SELECT [Occupancy].[PIN] FROM Occupancy WHERE [Occupancy].[PIN]='" & Me![ADDRESS3] & "' ;" sqlfre = "SELECT [Freeze].[PIN] FROM Freeze WHERE [FREEZE].[PIN]='" & Me![ADDRESS3] & "' ;"
Set bdg = db.OpenRecordset(sqlbdg, dbOpenSnapshot) Set swr = db.OpenRecordset(sqlswr, dbOpenSnapshot) Set wtr = db.OpenRecordset(sqlwtr, dbOpenSnapshot) Set dmo = db.OpenRecordset(sqldmo, dbOpenSnapshot) ''Set dvt = db.OpenRecordset(sqldvt, dbOpenSnapshot) Set occ = db.OpenRecordset(sqlocc, dbOpenSnapshot) Set fre = db.OpenRecordset(sqlfre, dbOpenSnapshot) Set swrlat = db.OpenRecordset(sqlswrlat, dbOpenSnapshot) Set wrtlat = db.OpenRecordset(sqlwtrlat, dbOpenSnapshot)
'Building recordset On Error Resume Next If bdg.EOF And bdg.BOF = True Then bdgCount = 0 Else
With bdg .MoveFirst .MoveLast bdgCount = .RecordCount End With
End If
'Sewer recordset On Error Resume Next If swr.EOF And swr.BOF = True Then swrcount = 0 Else
With swr .MoveFirst .MoveLast swrcount = .RecordCount End With
End If
'Water recordset On Error Resume Next If wtr.EOF And wtr.BOF = True Then wtrcount = 0 Else
With wtr .MoveFirst .MoveLast wtrcount = .RecordCount End With
End If 'Sewer laterial recordset On Error Resume Next If swrlat.EOF And swrlat.BOF = True Then countswr = 0 Else
With swrlat .MoveFirst .MoveLast countswr = .RecordCount End With
End If
'Water laterial recordset On Error Resume Next If wrtlat.EOF And wrtlat.BOF = True Then countwtr = 0 Else
With wrtlat .MoveFirst .MoveLast countwtr = .RecordCount End With
End If
'Demolition recordset On Error Resume Next If dmo.EOF And dmo.BOF = True Then dmocount = 0 Else
With dmo .MoveFirst .MoveLast dmocount = .RecordCount End With
End If
'Development recordset ''On Error Resume Next ''If dvt.EOF And dvt.BOF = True Then dvtcount = 0 ''Else
I am running this code, and i am getting this error:
Code:Private Sub SendFormToConsultants_Click() On Error GoTo Err_SendFormToConsultants_Click Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim stSubject As String '-- Subject line of e-mail Dim stCOFNumber As String '-- The COF Number from form Dim stCustomerID As String '-- The Customer ID from form Dim stCompanyName As String '-- The Company Name from form Dim stContactName As String '-- The Contact Name from form Dim stAddress As String '-- The Company Address from form Dim stTRDW As String '-- The TRDW from form Dim stPreReq As String '-- The PreReq from form Dim stWorkLoc As String '-- The Location of Work from form Dim stDelivActiv As String '-- The Deliverables/Activities from form Dim stStartDate As Date '-- The Start Date from Subform Dim stEndDate As Date '-- The End Date from Subform Dim stWho As String '-- Reference to Resources Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error '-- Combo of names to assign COF to stWho = Me.COF_Scheduled__Assigned_Resources__Subform1!Res ourceName stWhere = "Resources.ResourceName = " & "'" & stWho & "'" '-- Looks up email address from Resources varTo = DLookup("[ResourceEmail]", "Resources", stWhere) stCOFNumber = Me!COFNumber stCustomerID = Me.Consultancy_Order_Form_CustomerID stCompanyName = Me.CompanyName stContactName = Me!COFContact stAddress = Me.Address stTRDW = Me.TRDW stPreReq = Me.PreRequisites stWorkLoc = Me.WorkLocation stDelivActiv = Me.DeliverablesActivities stStartDate = Me.COF_Scheduled__Assigned_Resources__Subform1!Sta rtDate stEndDate = Me.COF_Scheduled__Assigned_Resources__Subform1!End Date stSubject = ":: New Consultancy Order Assigned ::" stText = "You have been assigned a new Consultancy Order." & vbCrLf & _ "Consultancy Order Form Number: " & stCOFNumber & _ vbCrLf & _ "Company ID: " & stCustomerID & _ vbCrLf & _ "Company Name: " & stCompanyName & _ vbCrLf & _ "Contact Name: " & stContactName & _ vbCrLf & _ "Address: " & stAddress & _ vbCrLf & _ "Terms of Reference / Description of Work: " & stTRDW & _ vbCrLf & _ "Pre-Requisites: " & stPreReq & _ vbCrLf & _ "Location of Work: " & stWorkLoc & _ vbCrLf & _ "Deliverables / Activities: " & stDelivActiv & _ vbCrLf & _ "Start Date: " & stStartDate & _ vbCrLf & _ "End Date: " & stEndDate & _ vbCrLf & _ "Please reply to confirm Consultancy Order Assignment." 'Write the e-mail content for sending to Consultant DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1 'Set the update statement to disable command button once e-mail is sent strSQL = "UPDATE [Consultancy Order Form] SET [Consultancy Order Form].COFSentToConsultants = 0 " & _ "Where [Consultancy Order Form].COFNumber = " & Me!COFNumber & ";" On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0 'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me!COFSentToConsultants.Requery Me!COFSentToConsultants.SetFocus Me.SendFormToConsultants.Enabled = False Exit SubErr_Execute: ' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If Resume NextExit_SendFormToConsultants_Click: Exit SubErr_SendFormToConsultants_Click: MsgBox Err.Description Resume Exit_SendFormToConsultants_ClickEnd Sub
What does it mean? it doesn't say where i have a problem in my code. What do you think?
All I want to do is run a make table query (or append) and show the user how many records were processed. If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".
I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.
strquery = "qryEmailGenerate" Set db = CurrentDb Set qdf = db.QueryDefs(strquery) Set rs = qdf.Execute txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf
I have an Excel file with a name range "DBIAS" which identifies all database data.
Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.
I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.
I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.
I am importing .txt files into Access table via VBA code (i.e., not via Saved Import Spec). Is there a way to trap the error if a particular field does not get imported due to incorrect format? When you import via Saved Import Spec and there are errors in formatting, Access generates an 'ImportErrors' table, which tells you which fields could not be updated.
Is there a way to generate a similar 'ImportErrors' table with VBA error checking?
I am getting this error message: "Unexpected Error from External Database Driver" when I try to import some .dbf files into Access. I just did some quick research, and it was suggested that I remove or rename the borland driver. Not sure how I feel about attempting that, plus, where do I get a new borland driver once the old is removed/renamed.
I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.
Code: Sub ExcelFormat() Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.worbooks.Open ("Z:DataTest.xlsx") End Sub
I have this function and I can't get rid of this error:"Compile error! External Name not defined"..This is just doing some calculations. I am just calling the function on the after update event on my form.
Code: Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double Dim Ttl3 As Double Dim Ttl4 As Double Dim Ttl5 As Double Dim Ttl6 As Double Dim Ttl7 As Double
I use the following code behind the BeforeUpdate event of a control, to validate input.
Code: Private Sub StockPrice_BeforeUpdate(Cancel As Integer) 'strings used for the MsgBox Dim strTitle As String Dim strMsg1 As String Dim strMsg2 As String Dim strMsg3 As String
[Code] ....
There are two different forms that use that control and this code behind the BeforeUpdate event.
It works on one but not on the other, allowing to input anything in the control with checking and without bringing up any msgbox. Searching what's the problem in the immediate window, brings up a message saying "External Name not defined'.
I am asking my users to interact with the Get External Data dialog, and I'm trying to restrict what they can do.
Code: DoCmd.RunCommand acCmdImportAttachText
I really like the mapping feature Access uses so I want to use that part of the Wizard, but I don't want my users to import data to the wrong table.Can I skip the first page of the 'Get External Data' Dialog series? Invoke the 'Import Text Wizard' without the previous dialog?see: AWF2. I can get the file path from a File Picker dialog.
I was wondering if it was possible to link random external tables to the Active Database through VBA. I would like to run the code that would open up a dialog box that would let the user select the database as well as the tables within that database that the user can select to link to. I am able to select the database and but not able to select the actual tables. The tables will be random so I can't make a constant statement for a specific database.
I have a database which contains a link to an external table. This link is broken, however, if the external table's database is moved (yes, I know this isn't a suprise).
What I want to know is if there's a way that would be easy for a user who's not very confident to easily change the path to the table. I don't care how--a popup box, a form, whatever, as long as it'll be easier for them than unhiding the main dtabase window, dleting the exiting link, and making a new one...
I have some code which creates a record in an external csv file to provide information to upload a vehicle to the website. It works fine with the exception of creating duplicates when, for example an option to amend a record is given to the user before continuing to a new record. The option has to be in place in case there is an error in the specifics of what is important to be recorded in the database and under such circumstances the form code allows the user to tab through the form and correct any errors, but this creates duplicate entries in the csv file as the user has to pass through the 'SavePrintVehPurch_Exit' button a second time after corrections are made.
I am wondering if there is a way of altering the following code to effectively look look to see if the csv record exists and if so correct any changed field information rather than create a whole new record or create a new record where the record does not exist - The record in the csv file is determined by the 'Me.VP_VehRegMark.Value '.
Private Sub SavePrintVehPurch_Exit(Cancel As Integer) Dim fileText As String Dim fileName As String Dim fileNumber As String fileNumber = FreeFile
[Code] ....
I am wondering about introducing the something like the following ( but can't work out how to clarify comparison of the fields)
If Len(filename(fileText)) = ? Then Do not change entry Else Overwrite the new detail
I have a table with an attachment field called email, where a .msg file is stored for each record. Looking for code to save this contents of this field in a folder in my drive.
I need to send commands to an external device using a serial port.My understanding is that that I need to select the reference to the Microsoft Communications Control (MScomm32.ocx).But is not available in the list of references!
