I have a function that was written in DAO against ms access mdb backend that i have converted to sql server backend with linked tables.
I have linked tables. The function uses transactions which i have already determined to not work with DAO and SQL Server based on experience and research. So i went about converting to ado.
the transactjon setup was
Dim NewCheck As New adodb.Recordset
Set TransTemp = DBEngine.Workspaces(0)
Set CurDb = TransTemp.Databases(0)
i am opening the recordset like this:
NewCheck.Open sqlstmt, CurrentProject.Connection, adUseClient, adLockBatchOptimistic
NewCheck![Trans Type] = "Check"
NewCheck![Account Number] = Account
NewCheck![Check Number] = NextCheckNum
NewCheck![Check Date] = Me.Check_Date
NewCheck![Payee] = ChecksToPay![Supplier Name]
NewCheck![Supplier Number] = ChecksToPay![Supplier Number]
NewCheck![Payment Amount] = PmtAmt
NewCheck![Deposit Amount] = 0
NewCheck![CurrencyCode] = CurCode
NewCheck![ExchangeRate] = ExChgRate
NewCheck![Bill Payment] = True
NewCheck![Posted] = True
NewCheck![Memo] = "some memo" ' for SQL Contraint
NewCheck![Posting GL ID] = "0" ' for SQL Contraint
NewCheck![Voucher] = "some voucher" ' for SQL Contraint. this is written later in function
NewCheck.MoveLast ', NewCheck.LastModified
It runs without any error, but does not actually write a new record at all.
Do i need a connection string or will currentproject.connection suffice? Is the DBEngine.Workspaces(0) workspace even the same workspace as my ado connection??