I am experiencing an intermittent problem with importing an XML file into access using VBA. The import works fine, but when re-run again at a later time it errors out with a Run-time Automation Error as per the attached image.
Here is the code to the point that it errors out. -
Function ImportRequest()
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim fld As Field
-
Dim fd As FileDialog
-
Dim objfl As Variant
-
Dim myImportFile, myPathName, strSQL1, strSQL2, strSQL3, strSQL4 As String
-
-
-
' Set MyDB to the current database.
-
Set db = CurrentDb
-
myPathName = db.Name
-
-
'Open file dialog to select file to import
-
Set fd = Application.FileDialog(msoFileDialogFilePicker)
-
With fd
-
.ButtonName = "Select"
-
.AllowMultiSelect = False
-
.Filters.Add "XML Files", "*.xml", 1
-
.title = "Choose Costing Request to import"
-
.InitialFileName = "S:\Phoenix Files\"
-
.InitialView = msoFileDialogViewDetails
-
.Show
-
For Each objfl In .SelectedItems
-
myImportFile = objfl
-
Next objfl
-
End With
-
Set fd = Nothing
-
-
'Import File to DB
-
Const acAppendData = 2
-
If Len(myImportFile) <> 0 Then
-
Set objAccess = CreateObject("Access.Application")
-
objAccess.OpenCurrentDatabase myPathName
-
objAccess.ImportXML myImportFile, acAppendData
I have released all variables at the end of the function, just in case it might have been the cause, but this made no difference. The only solution so far is to close and re-open the application between imports.
** Edit **
Are you aware that your code defines strSQL1/2/3 as Variant variables? I'm guessing not. Each variable must individually be defined as String if you want them all to be strings.
Are you aware also that lines #24 through #26 will ensure that only the last of the selected files is ever processed? That seems a strange requirement. Maybe it's not what you were expecting that code to do.
You are using Application Automation to open the same database again to try to handle your import. Is there any reason why you don't simply run it within the already open Access application?
You include loads of code which is only minimally relevant to the process, yet only the first couple of lines where it starts to become properly relevant. How about including the code where the work is actually done to clear down all that you set up to enable this to run. Simply telling us that you've released all variables at the end of the function gives us no way to find any errors in that code (and I would guess from what you've told us that the problem surely lies there).
4 3200 NeoPa 32,556
Expert Mod 16PB
Are you aware that your code defines strSQL1/2/3 as Variant variables? I'm guessing not. Each variable must individually be defined as String if you want them all to be strings.
Are you aware also that lines #24 through #26 will ensure that only the last of the selected files is ever processed? That seems a strange requirement. Maybe it's not what you were expecting that code to do.
You are using Application Automation to open the same database again to try to handle your import. Is there any reason why you don't simply run it within the already open Access application?
You include loads of code which is only minimally relevant to the process, yet only the first couple of lines where it starts to become properly relevant. How about including the code where the work is actually done to clear down all that you set up to enable this to run. Simply telling us that you've released all variables at the end of the function gives us no way to find any errors in that code (and I would guess from what you've told us that the problem surely lies there).
Hi NeoPa
I have now declared all string variables individually as suggested. The file selection behaviour is as expected, and I have AllowMultiSelect set to false.
I am not sure how to run the application automation from the already open access session, as I have tried to set the objAccess variable as currentDB and commented out the OpenCurrentDatabase line. I am still having the issue that the automation works, then chooses not to.
Please see my entire code for this funtion below. -
Option Compare Database
-
Option Explicit
-
-
Function ImportRequest()
-
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim rst As DAO.Recordset
-
Dim fld As Field
-
Dim fd As FileDialog
-
Dim objAccess As Application
-
Dim objfl As Variant
-
Dim myImportFile As String
-
Dim myPathName As String
-
Dim strSQL1 As String
-
Dim strSQL2 As String
-
Dim strSQL3 As String
-
Dim strSQL4 As String
-
-
-
' Set MyDB to the current database.
-
Set db = CurrentDb
-
myPathName = db.Name
-
-
'Open file dialog to select file to import
-
Set fd = Application.FileDialog(msoFileDialogFilePicker)
-
With fd
-
.ButtonName = "Select"
-
.AllowMultiSelect = False
-
.Filters.Add "XML Files", "*.xml", 1
-
.title = "Choose Costing Request to import"
-
.InitialFileName = "S:\Phoenix Files\"
-
.InitialView = msoFileDialogViewDetails
-
.Show
-
For Each objfl In .SelectedItems
-
myImportFile = objfl
-
Next objfl
-
End With
-
Set fd = Nothing
-
-
'Import File to DB
-
Const acAppendData = 2
-
If Len(myImportFile) <> 0 Then
-
Set objAccess = CreateObject("Access.Application")
-
objAccess.OpenCurrentDatabase myPathName
-
objAccess.ImportXML myImportFile, acAppendData
-
-
'RunSQL Query to append data to the Costing Table
-
strSQL1 = "INSERT INTO tblCostCalc ( strItemCode, strDescription, lngQtyReq, strFoilItem, " & _
-
"dblShimRepeat, lngStripWidth ) SELECT DISTINCT row3.ItemCode, row3.ItemDescription, " & _
-
"row3.Quantity, row1.U_c_typ_folie, row1.U_c_del_opak_kroku, row1.U_c_rozmer_s_v FROM row3, row1;"
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL1
-
DoCmd.SetWarnings True
-
-
'Remove records from imported xml tables
-
strSQL2 = "DELETE row1.* FROM row1;"
-
strSQL3 = "DELETE row3.* FROM row3;"
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL2
-
DoCmd.RunSQL strSQL3
-
DoCmd.SetWarnings True
-
-
'End If
-
End If
-
-
-
-
'Release variables
-
Set db = Nothing
-
Set rst = Nothing
-
Set fld = Nothing
-
Set fd = Nothing
-
Set objfl = Nothing
-
-
-
-
End Function
-
I appreciate your comments and suggestions.
Tony
Hi NeoPa,
The solution seems to lie with the CreateObject line. I managed to read your application Automation insight, and gleaned what was necessary from there. So far it appears to be working well.
Thanks
Tony
NeoPa 32,556
Expert Mod 16PB
When referring to the current application in any MS Office app you simply use the predefined object Application. IE. In this case that would be : - Call Application.ImportXML)myImportFile, acAppendData)
Clearly, it is possible for you to get the result using Application Automation, yet I believe this would be a mistaken approach. It would be somewhat akin to giving directions of three left turns for a destination that's just up ahead on the right. It gets you there, but by a circuitous route. TonyRandom:
The file selection behaviour is as expected, and I have AllowMultiSelect set to false.
In that case the code could be far simpler at : - myImportFile = .SelectedItems(0)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ange T |
last post by:
Hi there,
I'm having pain with the VB behind an Access form. The form is used to
create reports in Excel based on the details entered in the form. This
has always worked without error on my...
|
by: Andante.in.Blue |
last post by:
Our Access 97 database is a single-file (non-splitted, pure Access)
hosted no a network server, accessed regularly by a handful of users
around the office.
Until recently (until the Great...
|
by: Lisa |
last post by:
Can anyone help me with an "Automation Error" I am receiving in Access
XP? I am using the Combo Box wizard to create a combo box on a simple
form and receive the following error "Automation Error....
|
by: Tremendo |
last post by:
Hi,
I have two PCs:
PC1: Visual Studio 2005 (including .NET framework 2.0.
PC2: Visual Studio 6 (using Visual Basic 6) + .NET framework 2.0 installed separately.
On PC1 I wrote and built a C#...
|
by: Bruce |
last post by:
Hello,
I know this must be something simple I'm overlooking but I can't get
err.raise inside a class to return anything but 440 - automation
error. For example, if I create the following test...
|
by: deco |
last post by:
I try to list the contents of folder by usinng the FileSystemObject Library the
run-time error '-2147319779(8002801d)'; automation error library not registred
is appears but i use the same...
|
by: pqsoftware |
last post by:
Hi,
One of my users is getting 'run time 440' Automation error' when using the delete key in a textbox or sometimes when clicking on an item in a listbox with Windows XP.
I thought this may be...
|
by: =?Utf-8?B?VGVycnk=?= |
last post by:
Hi all,
I am trying to access a .net assembly from VB6. I have created the . Net
assembly, exposed it to COM and registered it. I am able to add a reference
to it in my VB6 project. When I...
|
by: nikhilkajrekar |
last post by:
For Each objObj In .CurrentProject.AllForms
DoEvents
intObj = intObj + 1
.DoCmd.OpenForm objObj.Name, acDesign, , , , acHidden
Set objForm = .Forms(objObj.Name)
With...
|
by: ricovox |
last post by:
Hello,
Every time I try to create an ADODB.Connection object, I receive the following error:
-----------
Runtime Error '-2147024703 (800700c1)':
Automation error
%1 is not a valid Win32...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |