Constants

[Guest]

Below is my constant and the code that uses it. This works great as it is.

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Projects\MyDatabase.mdb;Persist Security Info=False"

Dim Recordset As ADODB.Recordset
Dim vSQL As String

vSQL = "select * from CurrentData where GP = '" & vGrp & "' AND BR = '" &
vBr & "'"

Set Recordset = New ADODB.Recordset

Call Recordset.Open(vSQL, connectionstring, adOpenForwardOnly,
adLockReadOnly, CommandTypeEnum.adCmdText)

Worksheets(vSheet1).Activate
Call ActiveSheet.Range("A5").CopyFromRecordset(Recordset)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

However, I would like to use a variable in the Source section of the
constant because the file name will change each week (instead of hardcoding a
file name and path).

I haven't used constants at all, so I'm not sure if this is possible. If
you can do this, what would the syntax be? Any suggestions or code to help
with this situation would be greatly appreciated. Thanks for all of the
help.......

 

[Guest]

Just set you location to something like myString="c:/whatever" from a cell or
whatever you like then concantenate

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & myString & ";Persist Security Info=False"

 

[Guest]

Thanks........I'll give that a try. I was pretty sure there was a way to get
around this. Thanks again.

 

[Guest]

I have tried that and I get a compile error......"Constant Expresssion
Required".

Any thoughts on what I am doing wrong? Is it the location of the constant
in my code? Thanks again

 

[Dave Peterson]

That expression is not a constant.

dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& myString & ";Persist Security Info=False"

 

[NickHK]

Constants need to be, erm....constant. One way is to include a place holder
in the constant, and just replace that with the correct value when required:

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=**HOLD**;Persist Security Info=False"

Call Recordset.Open(vSQL, Replace(ConnectionString, "**HOLD**",
Range("A1").Value), adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText)

It may not be advisable to call your record set, buy that name in:
Dim Recordset As ADODB.Recordset

To avoid confusion call it something else, maybe "RS".

NickHK