1

I need to build a local .cub file for my Excel-using clients.

I have scrounged together some VB code but it fails :

ConnLocation = "LOCATION=C:\test.cub;"
ConnDSN = "SOURCE_DSN=DSN=TEST;UID=test;PWD=pass;"
ConnCreateCube = _
"CREATECUBE=CREATE CUBE [TestCube] (" & _
"DIMENSION [account_code]);"
Connection = CreateObject("ADODB.Connection")
Connection.Provider = "msolap"
Connection.ConnectionString = _
    ConnLocation & _
   ConnDSN & _
ConnCreateCube

I have trimmed this down to the above code and am getting a mysterious OLE DB error: OLE DB or ODBC error." when I try to run it.

Any help on the above or suggestions on a different way to approach this would me much appreciated.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
LenW
  • 3,054
  • 1
  • 24
  • 25

1 Answers1

2

Your connection string DSN property seems wrong:

ConnDSN = "SOURCE_DSN=""DSN=TEST;UID=test;PWD=pass;"""

Note the quotes.

I would recommend a small code change to make it more intuitive and fail-safe:

ConnLoc = "C:\test.cub"
ConnDSN = "DSN=TEST;UID=test;PWD=pass"
ConnSQL = "CREATE CUBE [TestCube] (DIMENSION [account_code])"

Connection = CreateObject("ADODB.Connection")
Connection.Provider = "msolap"
Connection.ConnectionString = "LOCATION=""" & ConnLoc & """;" & _
                              "SOURCE_DSN=""" & ConnDSN & """;" & _
                              "CREATECUBE=""" & ConnSQL & """;"
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • I still get the weird "OLE DB error: OLE DB or ODBC error." - the DSN is functioning and usable from other tools. Not really sure what error might be. Thanks for the neater syntax it does make it more readable. – LenW Apr 29 '09 at 08:35
  • Hm... Does it say nothing else? When looking for it in Google, these errors often seem to come with a description appended. Is it an exception? Maybe the stack trace tells you more? – Tomalak Apr 29 '09 at 08:57
  • The error is truly obscure and does not give much away about what is happening - e.g. errorcode is -2147467259 ? Do you have any references that might help me ? – LenW Apr 29 '09 at 10:25
  • -2147467259 is 0x80004005, which sounds a bit like "Access Denied" to me. – Tomalak Apr 29 '09 at 11:27