FredixBlog

If you are like me, always needing ADO recordsets but your memory is still weak?
Here are the lines of code to open ADO recordset against a MS-SQL Server database.
In addition, it address either the development server/database if the Access frontend is stored in a development environment, or the production database/server when in production. No need to bother about the SQL-Server database: When in development, your frontend’s folder just has to contain the word “Devel” in it.

' Declaring the ADODB objects
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

' Opening the connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open "driver={SQL Server};DSN=" & Get_Db(CurrentDb.Name) & ";server=" & Get_Server(CurrentDb.Name) & ";DATABASE=" & Get_Db(CurrentDb.Name) & ";APP=Microsoft® Access;Trusted_Connection=Yes;Connect Timeout=0"

' Opening the recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from [<name of the table>]", conn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then rs.MoveFirst

Here are the two 'environment sensitive' functions:

Public Function Get_Db(dbName As String)
    If InStr(UCase(dbName), UCase("Devel")) = 0 Then
     Get_Db = "<name of the ODBC DSN for the PRODUCTION database>"
    Else
        Get_Db = "<name of the ODBC DSN for the DEVELOPMENT database>"
    End If
End Function

Public Function Get_Server(dbName As String)
    If InStr(UCase(dbName), UCase("Devel")) = 0 Then
        Get_Server = "<name of the server for the PRODUCTION>"
    Else
        Get_Server = "<name of the server for the DEVELOPMENT>"
    End If
End Function

• Get Jet Connection String
• How can I e-mail a file from Microsoft Access?
• Custom Autonumber Access 2000

Hello Anna!

Thanks to ask your question at my website. I am pleased to answer to your question:

How can I e-mail a file from Microsoft Access to other people?

Here is a simple function which sends an email with an attachment.

Public Function MailWithAttachment(Recipient as String, Subject as String, Body as String, Attachment as String)
' Start Outlook.
' If it is already running, you'll use the same instance...
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

' Logon. Doesn't hurt if you are already running and logged on...
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

' Send a message to your new contact.
Dim olMail As Outlook.MailItem

Set olMail = olApp.CreateItem(olMailItem)
' Fill out & send message...
olMail.To = Recipient
olMail.Subject = Subject
olMail.Body = Body

olMail.Attachments.Add Attachment

olMail.Send
' Clean up...
'MsgBox "All done...", vbMsgBoxSetForeground
olNs.Logoff
Set olNs = Nothing
Set olMail = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing

End Function

Just paste this function in a module. Make sure you have a reference to ‘Microsoft Outlook 9.0 Object library’ checked in the
‘Tools/References’ menu, reachable from the module.

Test the function from the debug window by typing from instance:

?MailWithAttachment("mailbox@domainname.com"," _
   & "YourSubjectTextHere", " _
   & "YourBodyTextHere", " _
   & "c:\\MyFile.txt")

The parameters are easy:
Recipient = email address to sent your mail TO
Subject = Subject of your message
Body = Body text of your message
Attachment = filename for the attachment

Once the function is finished, you should have an email in your Outlook outbox, ready to be sent next time you check your email.

• GetOpenFileName()
• Create ODBC DSN
• Windows 7: Désactiver l’UAC
• Gibson plus solide que Fender?
• Get Jet Connection String
• Display a counter and remaining time in a loop
• Opening an ADO recordset
• Truncate long path names
• Check if file exists
• Create a linked table with VBA