Modules & VBA :: How To Create Error Log For Importing Data
Mar 19, 2014
I am using below code to import various Excel spreadsheets into Access. However every now and again below code goes into error, usually because there is no "toimport" range in the Excel file.
So I would like to use On Resume Next, so it can import all other files. But in order to do it, I need also to be able to somehow capture information about all files that went into error, so I can fix them.
I would like to create like an "error log" and I would like Access to update it with information about all the files that were not uploaded + error message + date/time.
Ideally I would like it to be in Excel file. I tried to use DoCmd.TransferSpreadsheet but it does not do what I need
Code:
Function import()
Dim strFile As String
DoCmd.SetWarnings False
' Set file directory for files to be imported
strPath = "C: est"
I am importing csv files into tables in a batch routine and I get the following error messages. The error number is always 2391.
Field 'F1' Doesn't Exist in Destination Table Field 'F4' Doesn't Exist in Destination Table Field 'F36' Doesn't Exist in Destination Table
I understand the first one and can find references to this on the web but the F4 and F36 escape me.
All fields required do actually exist in the table so the real problem is elsewhere.
P.S. I now think that it may be that there are embedded commas in one or more of the text fields and that the number following the 'F' indicates the position of the field in the table. I will check in the morning or create the csv files using commas and quotes.
I am trying to create a txt file to import into our accounting software. I get the file (its blank), but it fails on the WriteLine and i get the run time error. I have a command button on a form that the user will click to export the file.
Private Sub cmdExport_Click() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim strPath As String Dim strPathGB As String
I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.
I'm creating a DB that includes a form that "Customers" will fill out. The form (CustomerForm) has text boxs that must be completed. I'm trying to figure out how to make sure all text boxes are filled out. I have already created a Input Mask and selected Required in the "Customer" Table. There is also a Username text box I would like to have validated that is is not already being used and two password text boxes I would like to have validate against one another to ensure it was created correctly. Once all text boxes are properly filled out I would like the Button to Save customer data, close CustomerForm and Open ShoppingCart. This is the code I have created:
Code: Option Compare Database Private Sub Log_In_Click() If IsNull(Me.CustomerFName) Then MsgBox "Please Enter First Name", vbInformation, "First Name Required" Me.CustomerLName.SetFocus
Function SyncSuppliers() On Error GoTo errhandle Filename = DLookup("SupplierPath", "Setup", "SetupActive = True") If Filename = "" Then Exit Function End If Set xlapp = CreateObject("Excel.Application")
[code]....
The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.
what is the best way to import the data from the XML file into an access database table. The database I am working with has one large main table where all of the main record data is stored. There is a somewhat complex string of queries and reports based off this table that I am concerned about preserving. The problem is that the XML file is not structured in the same way the table is. The headings are named different, aren't in the same order, etc. I cannot use the import method and simply append it to the main table.After much searching around I have found two options:
1) Use the built in XML import method that access provides to create a secondary table. Then find a way to take data from individual fields in the second table and map and insert it into a new record in the main table.I already have the import part of this option working. The only part I can't seem to understand is how to take data from the second table and get it into a new record in the main table under the correct headings
2) Read the data from the XML file all at once and then map and insert it into the main table.I have not attempted this yet. I was having a hard time understanding how to retrieve the data from the XML file in the first place.
So.. which would be better/easiest to automate (most likely via button click on a form)? I only have a small understanding of VBA and even less understanding of anything XML.
Code beneath worked perfectly with Access 2003.Now we switched to Access 2010 and it generates a table where data isn't put into my 77 fields like before, but seperated into 1 field, separated by some ;;;.I am not good with programming.
Dim rst_data As Recordset Dim oldname As String, newname As String DoCmd.DeleteObject acTable, "TBL_import_TPXP_Radi_Evvd" DoCmd.TransferText acImportDelim, , "TBL_import_TPXP_Radi_Evvd", "N:APPLSHAREPRDQSIGMKTDISTPWBUSPB1815RADIEV IMPACT.CSV", False, ""
I have tried to create an MDE database and receive the message "Microsoft Access was unable to create an MDE database" I deleted all unnecessary tables, queries, forms and reports and still receive this message.
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
What I'm looking for is coding that would start with the first ID number in CONTACTS and see if there is the corresponding ID in DATA. If there is, then I want it to create an Outlook email and have:
1.To field populated with the email address in the CONTACTS table
2.From and CC fields to be my email address
3.The subject line needs to read [ID] "Request" and then today's date backward (e.g. 20150420).
4.The body I need to put some generic wording and then a table with the records from the DATA table (first 5 fields plus the headings).
5. The format of the table etc isn't important as long as it is a table format
If it's created the email I want it to go back to the DATA table and put "Email Created" in the Action column for those IDs.
I don't want the email to automatically send; I only want it to create a draft (as this data needs to be manually confirmed before sending).
If for example it gets to checking in DATA table for ID 002 (and as seen there's none there), I want it to skip creating the email and move on to the next ID.
In the end I would expect to see 3 draft emails open in Outlook ready to be sent, looking something like the below (just 001 below)?
From... my email To... Email1 CC... my email Subject: 001 Request 20150420
action the below:
ID Date Person Title Yes/No 001 20/4 Mr A Consultant 001 20/4 Mr B Supervisor 001 20/4 Mr C Consultant
And the DATA table look like this (if only 001 email was created):
ID Date Person Title Yes/No Action 001 20/4 Mr A Consultant Email created 001 20/4 Mr B Supervisor Email created 001 20/4 Mr C Consultant Email created 004 20/4 Mr D Consultant 010 20/4 Mr E Consultant 010 20/4 Mr F Supervisor
I will need to add more data to the CONTACTS table over time, also the DATA table will be different each day so I need the coding to accomodate for both of these possibilities.
I have an access table and I want the code that will check two columns in the table "EnvelopeType" and "EnvelopeSize" and create headers in Excelsheet automatically. In the attached workbook, like in sheet1 the headers are already appeared, I want this to be done dynamically using vba code so that if new values get inserted in EnvelopeType and EnvelopeSize then we won't have to change the code to display more headers.
Please see attached workbook named Sample and Access table. E.g.
EnvelopeType EnvelopeSize TNT 2nd Class C5 PP1 2nd Class C5 PPI 1st Class A4 Recorded A4 TNT 2nd Class C5 PP1 2nd Class C5 Recorded A4 PPI 1st Class A4 Recorded C5
With the code it should display following headers in excel sheet:
TNT 2nd Class C5 PP1 2nd Class C5 PPI 1st Class A4 Recorded A4 Recorded C5
I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:
Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).
Or there are column name spellings and in such cases, I get no error and the simply code hangs.
Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.
'Dim dbs As Database, tdf As TableDef Set dbs = CurrentDb On Error GoTo Macro1_Err DoCmd.SetWarnings False ' RunSQL executes a SQL string
I'm fairly new at access and have been stumped at this problem.
I download a csv file from the internet and what i wanted to do is just imported straight to a table from access. Some how the csv file contain some excel function that looks like the follwoing - i.e. =T("SOME TEXT"). Access can't import the file and i'm unsure of how i can do it. Does anyone have an idea of how i can do an import? Thank you.
I have a search query that searches for different results based on 3 criterior. I have set up a form so that the user can input the text into the form and then once the Submit button (that i created) is clicked the query table will be shown.
My question is:
Is it possible to have an error message box appear when i click submit and no data is returned by the search query?
I'm hoping for the message box to say "No corresponding records to your search criteria. Do you want to try again?"
Then the options given in the message box are Yes (where they should be taken back to the search form) and No (Where they are taken to another form).
I have a database that stores expiration dates of department charge numbers. The database emails personnel, that I enter into each record, at 15 and 30 days prior to expiration using SendObject.the database emails personnel, that I enter into each record, at 15 and 30 days prior to expiration using SendObject. I have a total of three fields where I enter an email address for each record; Email, E2, and E3. If I enter data in Email and E2 an email is sent. If I enter data in Email,E2, and E3 an email is sent. The problem happens when I only enter data in Email, I get an error 2295. I'm sure that it has something to do with the below piece of code and my use of Nz:
Have an Access db created with Access 2003. Was not updated with Access 2007, and now need to work with it in Access 2010.
Saved the Access 2003 db, a *.mdb file as a *.accdb and imported the data from the *.mdb db.
The import failed and the error report and in the report the Issue Type Error and the Issue Type ID is ACCWeb107014. When I checked this error, the follow explanation was given. However, the tables have a PK.
ACCWeb107014Error text A Table should have a Primary Key and it should be a number with field size 'long' to be compatible with the Web.What it means The indicated table has one of the following problems:
The table does not have a primary key.The data type of the primary key is not Number.The primary key Field Size is not Long.
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1 FROM [qryScheduleCombinedDetails] WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
I've vba in Report onformat the vba code does some data copying to External Excel file (premade by vba).Now on first run, i got success.But on opening the excel file, it showed blank space + Error"File already opened"
No White Spreadsheet is shown with data to see into file, i created new excel file in windows, and inserted the vba created/exported file as an Obj.Now Obj is showing correct and full data with spreadsheet as normal view.
When I try to open a recordset based on this sql, it gives me the runtime error - which is odd since I don't have any criteria in the statement.
I think the problem may be that vba is somehow adding a line break between "fullvals" and "18", but I don't know why it would do so and it doesn't always (only if the string is long).
So I have had Access for all of one day now. I am trying to import an excel file into access as a table. I successfully imported the first file but the second file gives me an error that says "An error occurred trying to import file ____. The file was not imported." What are the possible reasons this error has occurred? Both excel files I am importing are in the same format, so I am confused as to why one worked but the other did not. Thanks for any help.
I have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.
Each time I run my code I receive "run-time error 3021: No current record."
It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit" of my code. I'm suspecting that my function is running too fast, such that it did not have time to read that the table has already been populated by the timestamp in the AddNew code
Code: Private Sub Command9_Click() Dim dbs As DAO.Database Dim rstTimestamp As DAO.Recordset Dim rstAcknowledgement As DAO.Recordset Dim rstAgent As DAO.Recordset
