The Actinic Database

[JFro]

Just wondering if anyone has tried using the ActinicCatalog.mdb database in MS Access to run queries about their orders? I'm a bit scared about asking!

All I want to do is create a csv file of all orders placed after a certain date, so I can import them into the software for our dispatch company (or more precisely, Linnworks, which will talk to DespatchBay for me). This has to be in the form of a cross table query, otherwise I cannot get all the data required.

My first major problem has been with the format of the Order Date field in the Order table. It is formatted as Text, not as a Date/Time value. The second problem is that I cannot format the Order Date field in my QUERY to a Date/Time value because the 'Date' part of Order Date seems to mean something different to MS Access.

Any anyone has any clue about what I am on about, or better still, a suggestion... it would be much appreciated! Thanks

[Jan]

You could use our One Stop Order Processing plugin to export the data to a csv (we could probably add a link to DespatchBay to it as well if required).

You can find more details and a free trial here :

http://www.mole-end.biz/acatalog/Mul...r-Actinic.html

Regards,

[JFro]

Hello Jan, Yes I have looked at this... the problem is, we wouldn't need most of the other features OneStop offers, so I'm trying to find the most cost-effective solution. I have programming experience, but usually I have been able to directly edit the database (i.e give Order Date a different name and format it as a date) - but here I can't of course, for fear of upsetting Actinic itself...

It should be so simple?!!

[Mike Hughes]

You could do it in excel. You can run this as a macro.

Cells(1, "A").Select
    
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Pathtoyouractinicbd\ActinicCatalog.mdb;DefaultDir=C:\path" _
        ), Array( _
        "tothesitedirectory;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A8"))
        .CommandText = Array( _
        "SELECT `Order`.`Order Sequence Number`, `Order`.`Order Number`, `Order`.`Date Ordered`,`Order`.`Date Order Finished`, `Order`.`Order Total Cost`, `Order`.`Shipping Total Cost`" & Chr(13) & "" & Chr(10) & "FROM `Order` `Order`" & Chr(13) & "" & Chr(10) & "ORDER BY `Order`.`Order Sequenc" _
        , "e Number`")
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

Should do it. This will import the defined data for all orders but is easily edited or added to.

Remember to set the path to the actinic as highlighted in green.

Mike

PS. You might need to set excel to enable 'get external data' as this sometimes isn't installed by default.

PS2. As with anything. Backup before playing with the database. Only recommended for people who know what they're doing.

PS3. The tables / data being fetched here should be correct. You might need to check them against whichever version of Actinic you're using.

[JFro]

Not very often I copy and paste code and it works! That's great, thanks!

Can I just ask what this part does?

& Chr(13) & "" & Chr(10) & "FROM `Order` `Order`" & Chr(13) & "" & Chr(10) & "ORDER BY `Order`.`Order Sequence Number`")

It's the Chr part that is confusing, I can understand the rest I think

[leehack]

Quick Google search - http://www.techonthenet.com/excel/formulas/chr.php

[malbro]

Not very often I copy and paste code and it works! That's great, thanks!

Can I just ask what this part does?

& Chr(13) & "" & Chr(10) & "FROM `Order` `Order`" & Chr(13) & "" & Chr(10) & "ORDER BY `Order`.`Order Sequence Number`")

It's the Chr part that is confusing, I can understand the rest I think

chr(nn) generates a character from its ASCII code for example chr(65) will generate an A, normally only used as you see here to enter control characters such as carriage return (13) or line feed (10) which cannot be entered directly from the keyboard due to the way the keyboard strokes are interpreted. It's computer geek speak .

[Jan]

If you are only running SQL queries and exporting data then you shouldn't impact the Actinic database, if you are still worried though you could copy the database before exporting and then work on the copy.

Regards,

[JFro]

It's computer geek speak .

I'm good with that
Thanks for your helps guys. Hopefully I'll be able to manipulate the code now to get it to do exactly what I want it to do... fingers crossed!

Just to summarise (so people can find this topic when searching) this code, when run as a VB macro in Excel, allows you to export data or orders from the Actinic database (ActinicCatalog.mdb) into an Excel spreadsheet (or csv file). Alternatively, you can run a cross table query in Access and export the results to a csv file. This helps with despatch integration, as the csv file can be imported into your despatch software. It can be customised so you only export the information you need.

NB - do not change anything in the Actinic database tables, and as Jan recommends, work on a copy to try if out first. And back up...