SQL server integration with Outlook VBA

[RosH]

Dear All,

I have been trying to integrate Outlook with an SQL database, even
though I am a newbie to SQL server databases. The following is the
procedure code that I used for inserting a new row into a table called
Accounts. The code is working fine, but a hunch from the back of my
head tells me the code is not efficient and not according to the best
practices because I rely on creation of a string to do the database
operations. I would like to represent it to the outlook experts here
who can guide me to learn the best way to connect, insert or update a
database from Outlook VBA. Thanks in advance

-------------------------CODE---------------------------

Public Sub SQLInsertNewAccount(ByVal objAccount As Accounts)

' Declarations
Dim Dbcon As New ADODB.Connection
Dim Dbcom As New ADODB.Command

' Initialisations
Dbcon.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CRM;Data
Source=SERVER\SQLEXPRESS"
Dbcon.Open

' Command Object
Dbcom.CommandText = "INSERT INTO Accounts(RemoteID, OutlookID,
SyncStatus, Name, Address, City, PostalCode, Country, Phone, Fax,
WebPage, Description, Industry, EmployeeCount) VALUES ('" _
& objAccount.RemoteID & "', '" _
& objAccount.OutlookID & "', '" _
& objAccount.SyncStatus & "', '" _
& objAccount.Name & "', '" _
& objAccount.Address & "', '" _
& objAccount.City & "', '" _
& objAccount.PostalCode & "', '" _
& objAccount.Country & "', '" _
& objAccount.Phone & "', '" _
& objAccount.Fax & "', '" _
& objAccount.WebPage & "', '" _
& objAccount.Description & "', '" _
& objAccount.Industry & "', '" _
& objAccount.EmployeeCount & "')"


' Execution
Dbcom.ActiveConnection = Dbcon
Dbcom.Execute

' The End
Dbcon.Close
Set Dbcon = Nothing
Set Dbcom = Nothing

End Sub

 

[Michael Bauer [MVP - Outlook]]

For the database it doesn't matter if the data comes from Outlook, more
important is how much data it is and how often it will come in.

Best pratice depends on the environment, network; do you need transactions,
security etc. and would better be discussed in a database related newsgroup.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - Categorize Outlook data:
http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6

Am 18 Apr 2007 09:34:57 -0700 schrieb RosH: