?
Solved

ADODB Connection Error - Access 2007

Posted on 2011-10-02
2
Medium Priority
?
272 Views
Last Modified: 2012-06-21
Hi All,

A newbie to Access 2007, I am having issues with inserting records into a table due to connection type mismatches.

Could someone please help me arrange the correct syntax for the following procedure?

CF
Public Function createCustomer(uTxtNewCompanyName As String, uTxtLastName As String, uTxtFirstName As String, uTxtEmailAddress As String, _
uTxtJobTitle As String, uTxtBusinessPhone As Long, uTxtHomePhone As Long, uTxtMobilePhone As Long, uTxtFaxNumber As Long, uTxtAddress As String, _
uTxtCity As String, uTxtStateProvince As String, uTxtZIPPostalCode As Long, uTxtCountryRegion As String, uTxtWebPage As String) As Long
    'Creates a new customer in system
    'RETURNS the new customer id

    Dim rs As New ADODB.Recordset
    Dim Conn As Connection
    Set Conn = CurrentProject.Connection
    Dim strSQL As String
    Dim vCreationDTS As Date
    Dim vLastUpdateDTS As Date
        
    vCreationDTS = Now
    vLastUpdateDTS = Now

    strSQL = "INSERT INTO TBL_CONTACTS (Company, LastName, FirstName, EmailAddress, JobTitle, BusinessPhone, HomePhone, MobilePhone, " & _
    "FaxNumber, Address, City, StateProvince, PostCode, CountryRegion, WebPage, Attachments, Notes, CreationUserID, CreationDTS) " & _
    "SELECT '" & uTxtNewCompanyName & "' AS Company, '" & uTxtLastName & "' AS LastName, '" & uTxtFirstName & "'" & _
    " AS FirstName, '" & uTxtEmailAddress & "' AS EmailAddress, '" & uTxtJobTitle & "' AS JobTitle, " & uTxtBusinessPhone & _
    " AS BusinessPhone, " & uTxtHomePhone & " AS HomePhone, " & uTxtMobilePhone & " AS MobilePhone, " & uTxtFaxNumber & " " & _
    " AS FaxNumber, '" & uTxtAddress & "' AS Address, '" & uTxtCity & "' AS City, '" & uTxtStateProvince & "' AS StateProvince, " & uTxtZIPPostalCode & "" & _
    " AS PostCode, '" & uTxtCountryRegion & "' AS CountryRegion, '" & uTxtWebPage & "' AS WebPage, '" & vCreationDTS & "' AS CreationUserID"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    Set rs = Nothing

    strSQL = "SELECT @@IDENTITY FROM TBL_CONTACTS"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic

    createCustomer = rs(0)
    Set rs = Nothing

End Function

Open in new window

0
Comment
Question by:creativefusion
2 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36899263
What error/problem are you having?
0
 
LVL 9

Accepted Solution

by:
McOz earned 2000 total points
ID: 36899428
Change this:

 
Dim rs As New ADODB.Recordset 
    Dim Conn As Connection 
    Set Conn = CurrentProject.Connection 
    Dim strSQL As String 
    Dim vCreationDTS As Date 
    Dim vLastUpdateDTS As Date

Open in new window


To this:

 
Dim rs As New ADODB.Recordset 
    Dim Conn As ADODB.Connection 
    Set Conn = CurrentProject.AccessConnection 
    Dim strSQL As String 
    Dim vCreationDTS As Date 
    Dim vLastUpdateDTS As Date

Open in new window


And it should work.

Cheers,
McOz
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question