Database Connection fundamentals

[kimsMTC]

I am new to Access and am trying to understand the fundamentals of when to connect to a database. A lot of my confusion is because of my background in software development with much higher end languages. Here's my questions:

1. Since you create forms within an Access database, do you still need to connect to a database in the VBA scripting portion to do embedded SQL Select statements? I've tried my recordset code without a connection statement and I get an error. If I try to connect, it says that "; is already in use and won't recognize my full connection string.

2. If I am creating an Access application that needs security, it seems I need a login window that verifies username/password, make the database connection once, then use that connection throughout the app, and close the connection when the application closes. Is this correct? I can't get it to work.

Thank you!

Kim

 

[Crowley16]

when using recordsets, then each time you want to open a recordset, you will need to provide a connection.

However, it is enough to provide just a connection string, so what I do is to have 2 global variables, one to hold a static connection string, another one which only temporarily holds a connection string.

so upon login, I fill the temporary global variable with the connection string to use, and throughout my application, use the temporary connection string. If the program gets interupted, then memory is lots, and login needs to happen again before anything will work.

hope that makes sense...

--------------------
Procrastinate Now!

 

[CaptainD]

The basic way to connect to databases in the same Access "*.mdb, *.adp Project" would be like this:

Dim strSql As String
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
strSql = "SELECT Field1, Field2 FROM YourTable"
Set rs = db.OpenRecordset(stSql)

Do something with rs.Fields("Field1").Value etc.

rs.Close

You can also create a module with connection strings that point to other databases (Sql Server, other *.mdb etc) and access data that way. I have a connection Module with Functions that connect to different types of databases and with different methods Example:

Module:

Function GetADORS(strSql As String) As ADODB.Recordset

Dim objConn As ADODB.Connection

Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=YourServer;Initial catalog=YourDatabase;Integrated Security = SSPI;"

Set GetADORS = New ADODB.Recordset
GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly

End Function

To retrieve data

Private Sub MyButton_Click()
Error trap starts here
Dim strSql As String
Dim rs As ADODB.Recordset

strSql = "Some select statment"
Set rs = GetADORS(strSql)

Do something here

Set rs = Nothing
Exit Sub
Error trap handled here

End Sub

 

[Remou]

The usual thing with Access is linked tables. ADO is more common when using an Access or Access project front-end and some other database as a back-end.

 

[JoeAtWork]

If you use bound forms you don't need a connection object. In fact, you can do quite a bit without writing code.

If you need a connection to your current database, you never need to open one, there is always one available. As CaptainD has pointed out, if you want to use DAO, there is

CurrentDB

If you want an ADO connection, use

CurrentProject.Connection

Regarding your question on security, if you use Workgroups security (i.e. an MDW file), you create a shortcut to open Access with a command line parameter (/wrkgrp) that points to your security file. When you do this, Access displays a login dialog to type in the username and password.

 

[Melagan]

Using .mdw security also allows you to use Access' other built-in security features - like object permissions per user or per group. I also find the CurrentUser() function to be handy when logging who/when records are inserted/updated.

~Melagan
______
"It's never too late to become what you might have been.