Adding record with ACE.OLEDB vs SQL Server

[Garrett!]

Hello. Is there a difference when using Microsoft.ACE.OLEDB and SQL Server connection? I am using Access 2007 front-end and SQL Server 2014 for the back-end and ADO for my record set. When I started this project is used this statement:

connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

I want to change that to be using a SQL connection. My statement for that is:

connDB.Open "Driver={SQL Server};Server=LookData1;Database=UTOrderConfig;Trusted_Connection=Yes;"

What the project does is a salesman can choose the Brand, Model, and Make of an item. Then he can add customization to that by clicking the cmdCreateQuote. This opens the frmCustomQuote. On the frmCustomQuote there is the Brand, Model, Make, and Quote Number listed. Quote Number is supposed to be an auto number and is the primary key in the table. The salesman also has the ability to look up previous quotes from another screen. When I use the Microsoft OLEDB my code works fine.

Once I change over to the SQL Server, I don't get the Quote Number. My code always thinks the Quote Number is zero when the program is running. If I go to the table the item was added. Am I clear on what is happening?

So I guess what my question is do I have to do something different with the AddNew property?

 

[vbaInet]

You need to explain a bit more Garrett! Where's the code that you're using? And what action do you take before attempting to view the quote number, i.e. after adding a record? What about existing records, are you able to retrieve quote numbers?

 

[Garrett!]

Thanks for the reply! I appreciate it. I will try to explain what happens. This form loads when the user opens the database and is hidden from the user:

Option Compare Database
Option Explicit

'ADODB connection to the tbl_ActiveUsers
Public adoActiveUsers As New ADODB.Recordset
'ADODB connection to the Quotes table
Public adoQuotes As New ADODB.Recordset
'ADODB connection to the ztblUser
Public adoUsers As New ADODB.Recordset


Public connDB As New ADODB.Connection
Public Sub Form_Load()

On Error GoTo Err_Handle

'Declare Variables
Dim strPath As String
Dim strDBName As String
Dim strDB As String
Dim strSQL As String
Dim strWinUser As String
Dim strUser As String

    'Set the connection parameters
    strDBName = "UT Look Order Entry.accde"
    strPath = "U:\Kendall\Dev\OrderConfig\SQL-linked"
    strDB = strPath & "\" & strDBName
    'connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
    connDB.Open "Provider=SQLOLEDB;Server=LookData1;Database=UTOrderConfig;Trusted_Connection=Yes;"
    strWinUser = fOSUserName()
  
    'txtUserID.Value = strWinUser
    
    'Add New records to the table tbl_ActiveUsers.  This adds the Windows users and connection time
    adoActiveUsers.Open "SELECT * FROM [tbl_ActiveUsers]", connDB, adOpenDynamic, adLockOptimistic
    adoActiveUsers.AddNew
    adoActiveUsers.Fields("userid") = strWinUser
    adoActiveUsers.Fields("ConnectTime") = Now()
    adoActiveUsers.Update
        
    'Open the table ztblUsers
    adoUsers.Open "SELECT * FROM [ztblUsers]", connDB, adOpenDynamic, adLockOptimistic
    
    'Open the ADO connection for table tblQuotes
    adoQuotes.Open "SELECT * FROM [tblQuotes] ORDER BY [QuoteNum]", connDB, adOpenDynamic, adLockOptimistic
       
    'Find the connected user in the ztblUsers table
    With adoUsers
        .MoveFirst
        Do
            If adoUsers("userid").Value = strWinUser Then
                txtLname = .Fields("Lname").Value
                txtFname = .Fields("Fname").Value
                txtUserID = strWinUser  '.Fields("userid").Value
                txtLogID = adoActiveUsers.Fields("ID").Value
                txtFullNAme = txtFname.Value & " " & txtLname.Value
                'Set checkboxes
                bMulti = .Fields("MultiPlant").Value
                bDebug = .Fields("Debug").Value
                bAddModels = .Fields("AddModels").Value
                bUpdatePrices = .Fields("UpdatePrices").Value
                bUpdateStds = .Fields("UpdateStds").Value
                Exit Do
            End If
               .MoveNext
        Loop While Not .EOF
    End With

    'Minimize this form and open the Main Screen Form
    DoCmd.Minimize
    DoCmd.OpenForm "frmMainScreen", acNormal
    
Err_Handle:
    
    MsgBox Err.Description
    Resume Next

End Sub

This will load another form where they can click on a Create Quote Button. Once they click that it will open the form called OrderCreate. This form has unbound comboboxes. So the user would select the Brand of the item and that will filter the Models combobox, after a model is selected model number is selected.

The code for this form:

Private Sub cmdCreateQuote_Click()

'******************************************************
'   This button needs to accomplish the following tasks
'
'   1. Create a fully populated entry in tblQuotes
'   2. Using the quote# created in tbl Quotes,
'      Transfer the standards for the selected model to
'      tblQuoteConfigs.
'   3. Open Quote Customizing form so user can alter
'      the quote to their preferences.
'******************************************************

'Dim Variables
Dim strSQL As String
Dim intQuoteNum As Integer
Dim strFilter As String


'Create the ADO recordset for the table called tbQuoteConfigs
Dim adoQuoteConfigs As New ADODB.Recordset


    'Make sure the entry text boxes are not blank fields
    If IsNull(txtCompany) Then
        MsgBox ("Please select a Company")
        txtCompany.SetFocus
    ElseIf IsNull(txtPlant) Then
        MsgBox ("Please select a Plant")
        txtPlant.SetFocus
    ElseIf IsNull(txtModelType) Then
        MsgBox ("Please select a Model Type")
        txtModelType.SetFocus
    ElseIf IsNull(txtDealer) Then
        MsgBox ("Please select a Dealer")
        txtDealer.SetFocus
    ElseIf IsNull(txtModel) Then
        MsgBox ("Please select a Model")
        txtModel.SetFocus
    End If

    '******************************************************************************************
    '   1. Create a fully populated entry in tblQuotes
    '******************************************************************************************
        
       'Populate the table
       With Form_frm_SysControlForm.adoQuotes
            .AddNew
            .Fields("PlantCode").Value = txtPlant
            .Fields("ModelNum").Value = txtModel
            .Fields("DateCreated").Value = Now()
            .Fields("BaseCost").Value = txtBaseCost
            .Fields("CreatedBy").Value = Form_frm_SysControlForm.txtUserID
            .Fields("DealerID").Value = txtDealer
            .Fields("ModelName").Value = txtModelName
            .Fields("CompanyCode").Value = txtCompany
            .Update
        End With
        
        
    ' **************************************************************************************
    '   2. Using the quote# created in tbl Quotes,
    '       Transfer the standards for the selected model to
    '       tblQuoteConfigs.
    ' **************************************************************************************
    
        'Open the ADO connection for table tbQuoteConfigs
        adoQuoteConfigs.Open "SELECT * FROM [tbQuoteConfigs]", Form_frm_SysControlForm.connDB, adOpenDynamic, adLockOptimistic
                                
        intQuoteNum = Form_frm_SysControlForm.adoQuotes.Fields("QuoteNum").Value
        'Statement to add records into the table tbQuoteConfigs.  These are taken from the form sfrm_ModelStandards
        With adoQuoteConfigs
            .AddNew
            .Fields("QuoteNum").Value = intQuoteNum
            .Fields("ModelNum").Value = Form_sfrm_ModelStandards.ModelNum
            .Fields("Category").Value = Form_sfrm_ModelStandards.Category
            .Fields("SubCategory").Value = Form_sfrm_ModelStandards.SubCategory
            .Fields("Description").Value = Form_sfrm_ModelStandards.Description
            .Fields("Cost").Value = Form_sfrm_ModelStandards.Cost
            .Update
        End With
        
        
        'Execute SQL statement
        'DoCmd.RunSavedImportExport strSQL
        
    ' **************************************************************************************
    '   3. Open Quote Customizing form so user can alter
    '       the quote to their preferences.
    '****************************************************************************************
    
        
        strFilter = "[QuoteNum] = " & CStr(intQuoteNum)
        'Load the frmCustomQuote
        DoCmd.OpenForm "frmCustomQuote", , , strFilter
        'Load values in frmCustomQuote
        With Form_frmCustomQuote
            .DateCreated = Form_frm_SysControlForm.adoQuotes.Fields("DateCreated").Value
            .PlantCode = Form_frm_SysControlForm.adoQuotes.Fields("PlantCode").Value
            .ModelNum = Form_frm_SysControlForm.adoQuotes.Fields("ModelNum").Value
            .BaseCost = Form_frm_SysControlForm.adoQuotes.Fields("BaseCost").Value
            .CreatedBy = Form_frm_SysControlForm.adoQuotes.Fields("CreatedBy").Value
            .DealerID = Form_frm_SysControlForm.adoQuotes.Fields("DealerID").Value
            .ModelName = Form_frm_SysControlForm.adoQuotes.Fields("ModelName").Value
            .CompanyCode = Form_frm_SysControlForm.adoQuotes.Fields("CompanyCode").Value
        End With

        DoCmd.Close acForm, "OrderCreate"
       
        'Close the adoQuoteConfigs connection
        adoQuoteConfigs.Close
        Set adoQuoteConfigs = Nothing
    
    

End Sub

Now a form called frmCustomQuote opens where the user can add customization to the item. There is a text box called QuoteNum here that is supposed to be populated. If I use the SQL connection is does not. If I use the OLE it does. The code:

'Dim the Variables
Dim strSQLCustom As String
Dim strSQLConfigs As String
Dim iQuoteNum As Integer


Private Sub Form_Load()
    
    'SQL statement for the ADODB connection adoQuotesCustomQuote
    strSQLCustom = "SELECT * FROM [tblQuotes]"
    'Configure the ADODB connection adoQuotesCustomQuote
    adoQuotesCustomQuote.Open strSQLCustom, Form_frm_SysControlForm.connDB, adOpenDynamic, adLockOptimistic
    
    'SQL statement for the ADODB connection adoQuoteConfigsCustom
    strSQLConfigs = "SELECT * FROM [tbQuoteConfigs]"
    'Configure the ADODB connection adoQuoteConfigsCustom
    adoQuoteConfigsCustom.Open strSQLConfigs, Form_frm_SysControlForm.connDB, adOpenDynamic, adLockOptimistic
    
    
End Sub


Private Sub cmdChangeModel_Click()

    'Open the form mfrmUpdateModelInfo
    DoCmd.OpenForm "mfrmUpdateModelInfo"
    
    'Take the text from the frmCustomQuote form to populate the comboboxes on sfrm_ModelStandards
    Form_mfrmUpdateModelInfo.txtQuote = txtQuote
    Form_mfrmUpdateModelInfo.txtNewNumber = ModelNum
    Form_mfrmUpdateModelInfo.txtDescription = ModelName

Now I think the problem might be with a line of code in OrderCreate form that goes:

strFilter = "[QuoteNum] = " & CStr(intQuoteNum)

If I take this out I get a quote number, but it's an already existing number. It should be a new one.

Thanks again. Sorry for the long, convoluted post.

 

[vbaInet]

I'm guessing QuoteNum is an auto number? Use @@IDENTITY to get the last created QuoteNum with the SQL Server driver.

Use "SELECT @@IDENTITY" with the same connection object that you used to add the new record.