Insert Qlikview straight table data into access database

[desertmatt]

Dear All,

After I managed (with your help) to "pull" data from Qlikview into Excel (with Excel VBA), I need now to push from inside Qlikview (VB script linked to a button on the sheet) to an Access database.

I set up the database connection:

Set DbConn = CreateObject("ADODB.Connection")

StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= test.accdb;Persist Security Info=False;"

DDbConn.Open StrConn

and identified the Straight table in Qlikview:

set TableBox = ActiveDocument.GetSheetObject("CH195")

then I got the TableBox.GetRowCount for later stepping through each row (record) and insert into the Access table

But now I'm stuck and hope that one or some of you can get me back on track:

1. How to set the access recordset - I tried:

Set DB = currentDB()

SqlStr = _

"SELECT * " & _

"FROM PRODUCTION"

Set rs = db.OpenRecordset(SqlStr, dbOpenDynaset)

but this is not working

If I get this working, I plan to loop through each record from TableBox and insert into the "Production" table in Access

Quite lost though...

hope someone can help me

Kind regards

Matt

[marcus_sommer · Accepted solution]

To write data into a database you need an insert- or an update-statement and not a select which only queried exists data. Here a few examples from the community:

Re: QlikView data into SQL

Insert / Update data into MSSQL

Maybe it's an alternatively to overwrite with your export or maybe a store-statement an existing csv-file which Access used as an external data-sources.

- Marcus

[desertmatt]

Dear Marcus,

Thank you so much! From the links provided I could reconstruct below code which beautifully works with an access 2016 database.

sub DbWrite()
dim sServer, sConn, oConn, oRST

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Documents\test-DB-CONNECT.accdb;Persist Security Info=False;"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn

Set oTT =CreateObject("ADODB.Recordset")

oTT.open "table1", oConn

Set oRST =CreateObject("ADODB.Recordset")

'First empty the table (Delete all records)
oRST.open "DELETE * FROM table1", oConn

    set table = ActiveDocument.GetSheetObject("CH01")
    For RowIter = 2 to table.GetRowCount-1
            set ProductNo = table.GetCell(RowIter,0)
            set Price = table.GetCell(RowIter,1)
            set Wt = table.GetCell(RowIter,2)
            set PCS = table.GetCell(RowIter,3)

SqlStr="INSERT INTO table1 (ProductNo,Wt,Price,PCS) values('" & ProductNo.Text & "','" & Wt.Number & "','" & Price.Number & "','" & PCS.Number & "')"
oRST.open SqlStr, oConn
   
    next

oConn.Close

end sub

Now I have one more "stupid" question - couldn't find this anywhere. I wanted to add a field "Production Date" in Qlikview and Access.

But if I add this to the sqlStr like ProdDate.date it throws an error "Object doesn't support this property or method: 'ProdDate.date' " - if I make it ProdDate.text I get a type mismatch.

How to specify the field as date field in the SqlStr - hope I don't embarrass myself not too much 😉

Thank your very much again for your support

Kind regards

Matt

[marcus_sommer]

You need separate statements to the insert statement to create or alter tables/fields with SQL, for example:

ALTER TABLE table1 ADD "Production Date" DATE

A good starting point fr it could you find here: ALTER TABLE-Anweisung (Microsoft Access SQL)

But in QlikView you couldn't add a table or a field from the macro - they must be created within the load-script.

- Marcus

[desertmatt]

Thank you Marcus,

This is also interesting but maybe a misunderstanding? I'm at the beginner level (ALTER table I can try later maybe...;-))

All I did was to add one Date field in the Qlikview and ACCESS tables (manually - not by code)

All the other fields in my code were either .txt or .number format - but now if I want to write the data from Qlikview to Access with the extra date formatted field,  I get the above errors and I just need to know how to write the "ProdDate.???" field in the SqlStr - to avoid the errors?

Hope I was more clear this time (Or maybe I didn't understand the Alter table method??)

Thank you again

Kind regards

Matt

[marcus_sommer]

Ok. I understand it now. ProdDate.date isn't a valid statement because getcell hasn't any datatype else each content will be treated as string or number. This meant you need to use ProdDate.text. The mismatch-error happens because the date-format from QlikView is different to them from the Access field.

I'm not sure if Access and/or the oledb driver will convert a date-string like 17.11.2017 into 43056 which is the real value which would be stored in Access for a date even if it will be displayed as (anyhow defined) date-format-string. You could try some trial & error which way worked or you just used a pure numeric value like 43056 for it either within your qlik-field or per converting within the macro - maybe per cdate() or some other logic.

- Marcus