asked on

currentproject.connection vs connection string ms access / sql server

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)
TransTemp.BeginTrans

i am opening the recordset like this:

NewCheck.Open sqlstmt, CurrentProject.Connection, adUseClient, adLockBatchOptimistic

NewCheck.AddNew
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.Update
NewCheck.MoveLast ', NewCheck.LastModified

TransTemp.CommitTrans

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??

Press

Kelvin Sparks

I start with
Dim cnn As ADODB.Connection

Then
Set cnn = CurrentProject.Connection

and finally

NewCheck.Open sqlstmt, CurrentProject.Connection, adUseClient, adLockBatchOptimistic

Have you explicitly Dim'd the connection as ado as you have done with the Recordset?

Kelvin

pressMac

ASKER

This looks like you create the cnn object, but then don't use it when opening the recordset.

NewCheck.Open sqlstmt, cnn, adUseClient, adLockBatchOptimistic.

So i tried it anyway, and then that make another statement break earlier in the code like so:

ChecksToPay.Open sqlstmt, CurrentProject.Connection

"Run-TIme error '3251', current provider does not support the necessary interface for Index functionality."

pressMac

ASKER

PS: the ChecksToPay is dim'd this way:
Dim ChecksToPay As New ADODB.Recordset

pressMac

ASKER

in this function some of the recordsets opened are in SQL Server, and others are in a local tmp .mdb file. So the currentproject.connection points to the frontend file that is open. So how to i open directly without odbc, the sql table and the tmp table?

ASKER CERTIFIED SOLUTION

pressMac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pressMac

ASKER