How to Declare Const in Access VBA

[ped]

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.

[ajetrumpet]

public constants = visible from any module
private constants = visible in the constant's module only

for public:

public const variableName as datatype = value

[ped]

ajetrumpet, that was cool...the public thing make it available in all the module...thanks a lot!!!

I just need one more help...

I want one const connection be there to another database...
how can i declare that...

I tried this way and wont work..

Thanks again.
Pedie

Const MyConnection = Connection.Open = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & db & ";Uid=;Pwd=123;"

public constants = visible from any module

private constants = visible in the constant's module only

for public:

public const variableName as datatype = value

[ajetrumpet]

you want to use ADO to connect to another mdb database? you can use either that or DAO. it doesn't matter.

constants are only used for values. a connection is not a value, so no you can't use constants to do that. however, I believe you can always keep a connection open...for the entire length of the period that you have an mdb open. There might be a default timeout on established connections with ADO, but I don't know of it if there is.

and as far as I know, you can't simply use connection.open arbitrarily in vb without declaring an ADO object first. for instance, you need to use:

dim conn as adodb.connection
etc, etc...

and by the way, simply using "const" instead of "public const" is the same thing in vb. the default specification for constants is public, just like the default spec for routines.

[ped]

ajetrumpet, thank you very much!!!
So to confirm, to open a different database besides the one i'm using i can use any type of connection like
ADO
ADODB
DAO etc? and it does not matter right?

Suppose i'm using connection like this to open my other db...
How do i refer to that db and recordset?

dim mydb as adodb.database?
I tried and dont know what to do...
'm new to access.

Thanks again for helping...
Regards
Pedie

Dim cnn As New adodb.Connection
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Personal_Files\Access\MAINDB.accdb;Jet OLEDB:Database Password=123;"

you want to use ADO to connect to another mdb database? you can use either that or DAO. it doesn't matter.

constants are only used for values. a connection is not a value, so no you can't use constants to do that. however, I believe you can always keep a connection open...for the entire length of the period that you have an mdb open. There might be a default timeout on established connections with ADO, but I don't know of it if there is.

and as far as I know, you can't simply use connection.open arbitrarily in vb without declaring an ADO object first. for instance, you need to use:

dim conn as adodb.connection
etc, etc...

and by the way, simply using "const" instead of "public const" is the same thing in vb. the default specification for constants is public, just like the default spec for routines.

[ajetrumpet]

ADODB is the syntax for ADO. I would use DAO to open another db, personally.

It would look like so:

Sub whatever()

Dim db As dao.Database
Dim rs As dao.Recordset
Set db = OpenDatabase("path")
Set rs = db.OpenRecordset("table")

'do whatever to the table

rs.Close
db.Close

End Sub

The only reason you would be concerned about the technical differences between ADO and DAO is if you were on a large network or you were offering remote access/ODBC to users. Other people will say differently, but in reality it doesn't matter all that much too often. For instance, I work for a government contractor and government will complicate anything out of anything, so of course I have to watch out for this stuff. But I know enough to know that to someone that's not managing a corporate network of machines, the difference between ADO and DAO is marginal, at best.

[ped]

ajetrumpet, I dont know how to open the databse with password...
Please help out here again...
And so to confirm, it does not matter which connection string i use it can be ADO/DAO/JET etc I can still perform task in my other databse?

Thanks again ...appriciate your help

Sub test333_1()
Dim cnn As New adodb.Connection
Dim db As dao.Database
Dim rs As dao.Recordset
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Personal_Files\Access\MAINDB.accdb;Jet OLEDB:Database Password=123;"
Set db = OpenDatabase("E:\Personal_Files\Access\MAINDB.accdb")
Set rs = db.OpenRecordset("BTLog")
 rs.MoveFirst
 While Not rs.EOF
 Debug.Print rs.Fields(1)
 rs.MoveNext
 Wend
rs.Close
db.Close
cnn.Close
Set cnn = Nothing
End Sub

[ped]

Just calrification...

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Personal_Files\Access\MAINDB.accdb;Jet OLEDB:Database Password=123;"

If i use OLEDB.12.0 as connection...how do i open that/different database and recordset

Thanks again

[ajetrumpet]

hello again Girly,

Honestly, I don't want to provide any more help for ADO because I don't like it. So the DAO equivalent to what you're doing is this:

Sub test333_1()
 
Dim db As dao.Database
Dim rs As dao.Recordset

Set db = OpenDatabase("E:\Personal_Files\Access\MAINDB.accdb", False, False, "MS Access;PWD=password")
Set rs = db.OpenRecordset("BTLog")
 rs.MoveFirst
 While Not rs.EOF
 Debug.Print rs.Fields(1)
 rs.MoveNext
 Wend
rs.Close
db.Close
 
rs.Close
db.Close
 
End Sub

You should also read some help files in access and on the net. Educate yourself and you should be fine. For instance, since you're hung up right now on the OPEN method of the connection object, simply type in vba help "connection.open" and you'll see the help file for ADO right at the top. Since you're using the ACE engine, you either have version 07 or 2010. Some good reading for you would be either of these to start:

http://support.microsoft.com/default...b;EN-US;209953

http://msdn.microsoft.com/en-us/libr...(v=vs.71).aspx

Connection strings come from library (DLL) resources, like ADO and DAO. JET and ACE are database engines. They do the internal work for requests from users, like queries and such. JET/ACE is not really part of programming and automating. ADO and DAO are. Don't get them confused.

[ped]

Thank you thank you ajetrumpet, thanks a alot!
You have a great day! perfect!