Hi all. Seems like I only get around to this stuff on a Friday.
I've had assistance from this forum previously in relation to importing an XML file in to my DB. Whilst what I have now works well, the issue is that the bulk of the information being imported from the XML file is not required, so gets deleted (automatically) after the import and I am left with what I want. The issue here is that this process is seriously blowing out the DB. Once the project is completed and rolled out to the multiple users I can foresee that the DB will rapidly become enormous.
e.g The DB is initially 0.55MB, however after importing the data from just 5 XML files, creating 5 rows of data in the destination table, the DB is now 1.86MB in size. Running Compact & Repair returns the DB to .55MB (yes the data is there, there just isn't a lot of it - mostly numbers.)
The data from the XML file ultimately ends up in 1 table with 28 fields. This table will then be linked to a a couple of other tables in the project.
I toyed with the idea of running Compact & Repair after each import from the XML file, but this is apparently not possible through VBA and can be problematic if there are multiple users logged in to the DB at the time. Manually running Compact & Repair works, but most of the end users are beyond this sort of activity and I'd prefer if they didn't have access to the Access menus.
I feel like the Application.ImportXML function is the root of the issue, because it imports the whole of the XML file, creating 20 tables, when I only want to import a couple of sections of the XML file.
This is the SQL for the query that imports the information in to the destination table.
I guess my question is, is there a way of just importing sections of the XML file (based on their section names), or parsing the XML file to pick out the data I want?Code:INSERT INTO XMLMachineSettingsImportTemp ( MachineName, A2MCNo, MachineNo, FeedRateMAX, PlungeRateMAX, TravelRateMAX, TravelRate, PlungeRate, FeedRate, JogSpeedMode, SeekXYSpeed, SeekZSpeed, JerkGrate, AccelerationG, AccelMAX, JerkMAX, CentripetalG, BrakeG, ArcError, MinLength, G56x, G56y, G56z ) SELECT MachineInformation.MachineName, Model.Code, Left([MachineName],5)+Right([MachineName],4) AS Expr1, MotionParameters.FeedRateMAX, MotionParameters.PlungeRateMAX, MotionParameters.TravelRateMAX, MotionParameters.TravelRate, MotionParameters.PlungeRate, MotionParameters.FeedRate, MotionParameters.JogSpeedMode, MotionParameters.SeekXYSpeed, MotionParameters.SeekZSpeed, MotionParameters.JerkGrate, MotionParameters.AccelerationG, MotionParameters.AccelMAX, MotionParameters.JerkMAX, MotionParameters.CentripetalG, MotionParameters.BrakeG, MotionParameters.ArcError, MotionParameters.MinLength, Origin.x, Origin.y, Origin.z FROM MachineInformation, MotionParameters, Model, Origin;
Should I be taking another approach?
BTW, i have no control over how the XML file is created, they will be emailed from sources all around the world.
Any suggestions greatly appreciated.