[RESOLVED] What is wrong with this ADO statement

[jonrmoore]

I need to be flexible when I open the mdb at customer sites. The line that is commented out works fine but the one I am trying under it errors:

Option Explicit
'Private Const CNXSTRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Aces Projects\GL\database\AcesGL.mdb;Persist Security Info=False"
Private Const CNXSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

Private cnx As ADODB.Connection
Private rs As ADODB.Recordset
Private aRs As ADODB.Recordset
Private Prs As ADODB.Recordset

I am following a textbook; however, I get an error saying:
"Constant Expression Required"

App.Path is correct and has a value of "C:\Aces Projects\GL"

Thanks in advance for help.

[riteshjain1982]

change

Private Const CNXSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

to

Private CNXSTRING  as string= "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

[riteshjain1982]

as you can not concatenate two string in declaration if variable is of type CONST

[jonrmoore]

Sorry, it still gives me an error. It now highlights the "=" and says "expected end of statement". I think I copied it correctly:

Private CNXSTRING as string= "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

[riteshjain1982]

not sure what went wrong but just give one more try with below

Private CNXSTRING as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

[jonrmoore]

Same result

[riteshjain1982]

what language you are using?is it vb.net?

[jonrmoore]

No, it is vb6

[riteshjain1982]

ok,not very much familiar with vb6 syntax but one of below should work..

dim CNXSTRING as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

or

dim CNXSTRING  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"

[jonrmoore]

I got it working with some help. I had to declare the variable first. I then loaded the concatenated value to it separately and it now works! Thanks for responding.

[riteshjain1982]

Glad to know your problem is solved,it would be good if you can post your solution so it can be helpfull to someone else in future

[jonrmoore]

Well, this does work if you "hardwire" the database location:

'Private Const CNXSTRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\GL.mdb;Persist Security Info=False"

This does not work but I am not sure why. It has something to do with value restrictions I was told.

[CODE]
Private CNXSTRING as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"
[CODE]

So, to make it work, in the top of the form, I put:

Private CNXSTRING As String

And in the form_Initialize routine I put this"

    Set cnx = New ADODB.Connection
    CNXSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\database\AcesGL.mdb;Persist Security Info=False"
    cnx.Open CNXSTRING

Hope this helps someone.

[si_the_geek]

That behaviour is expected..

App.Path is not a fixed value (it can change each time you run the program), so is not a constant value - and therefore the compiler cannot hard-code it.

In Classic VB you cannot declare and set a variable in one line of code, you need to use separate declaration and assignment statements (but Consts are the opposite).