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.
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.
public constants = visible from any module
private constants = visible in the constant's module only
for public:
Code:public const variableName as datatype = value
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
Code:Const MyConnection = Connection.Open = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & db & ";Uid=;Pwd=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:
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.Code:dim conn as adodb.connection etc, etc...
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
Code: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;"
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:
Code: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.
ADODB is the syntax for ADO. I would use DAO to open another db, personally.
It would look like so:
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.Code: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
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
Code: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
Just calrification...
Code: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
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:
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:Code: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
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.
Thank you thank you ajetrumpet, thanks a alot!
You have a great day! perfect!