Constants

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JT - 25 Jan 2007 19:15 GMT

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.......

Signature

Reply to this Message

John Bundy - 25 Jan 2007 19:31 GMT

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"

Signature

-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.

> Below is my constant and the code that uses it. This works great as it is.
>
[quoted text clipped - 25 lines]
> with this situation would be greatly appreciated. Thanks for all of the
> help.......

Reply to this Message

JT - 25 Jan 2007 19:36 GMT

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

Signature

> Just set you location to something like myString="c:/whatever" from a cell or
> whatever you like then concantenate
[quoted text clipped - 31 lines]
> > with this situation would be greatly appreciated. Thanks for all of the
> > help.......

Reply to this Message

JT - 25 Jan 2007 19:41 GMT

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

Signature

Reply to this Message

Dave Peterson - 25 Jan 2007 21:14 GMT

That expression is not a constant.

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

> I have tried that and I get a compile error......"Constant Expresssion
> Required".
[quoted text clipped - 46 lines]
> > > --
> > > JT

Signature

Dave Peterson

Reply to this Message

NickHK - 26 Jan 2007 04:17 GMT

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

> I have tried that and I get a compile error......"Constant Expresssion
> Required".
[quoted text clipped - 37 lines]
> > > with this situation would be greatly appreciated. Thanks for all of the
> > > help.......

Reply to this Message