How to Declare Const in Access VBA

[pedie]

Hi, how do I Declare Const in Access VBA...
Specially the Connection to different database...so that i can refer to this const from any Module?
Thanks a lot in advance.

 

[xenou]

Just as in Excel. In a Standard Module:

Public Const DB1_PATH = \\Server1\Folder1\DB1.mdb"

 

[pedie]

That works perfect...like in excel
Is it possible to add connection,sstring,id,pass etc in Const?
so that everytime i want to connect to other database i just refer to that const connection from my module?


Xen, thanks again for all the help...
I think i'm learning fast

 

[Norie]

Pedie

Of course it works, Access VBA and Excel VBA are basically the same.

The main difference is the object model.

In Access you have databases, tables, queries, forms etc.

I Excel you have workbooks, worksheets, ranges, charts etc.

 

[pedie]

Thanks Norie for clarifying for me...

'm trying to declare it this way and make it work...please advice now do i refer to this kind of const from my module?

Thanks again

[/FONT]
[FONT=Courier New]Public Const mydb1 = cnn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Personal_Files\Access\MAINDB.accdb;Jet OLEDB:Database Password=TsPw;")[/FONT]
[FONT=Courier New]

 

[Norie]

pedie

You can't refer to a constant like that in VBA, whether it's Access/Word/Excel/Whatever.

cnn.Open... is not a constant.

 

[pedie]

Thanks again Norie...
You are right...
I thought maybe it is possible...

Thanks!
Pedie

pedie

You can't refer to a constant like that in VBA, whether it's Access/Word/Excel/Whatever.

cnn.Open... is not a constant.

 

[xenou]

The closest you can get with a constant is:

Public Const DB1_CONNECTION_STRING As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Personal_Files\Access\MAINDB.accdb;Jet OLEDB:Database Password=TsPw;"

Then in your code:

Sub Foo()
Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Call rs.Open(DB1_CONNECTION_STRING)
    '//More code
    rs.Close
    Set rs = Nothing
End Sub

 

[pedie]

Perfect....Xen, perfect! all this a new to me...
Thanks a alot!!

 

[Norie]

Pedie

What are you using this code for?

It seems a bit strange to be using ADO in Access to work with another Access database.

Have you checked if there's anything in Access that would allow you to do whatever you are doing without code?