0

I'm using an Access front-end for an Microsoft SQL back-end.

The problem I'm facing, is that I'm inserting data with an adodb connection. I use this on multiple forms. On the first form it works, on the second form it works, on the third form it also works. But on the fourth form I get an: 'ODBC: Call failed' error.

You might think I made some kind of typing error, but that is not the issue. When I start with form four I can insert the data. So long story short, after 3 inserts on different form, I get that odbc error. I don't know what the problem is. 

 Dim Query As String

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Query = "SELECT MAX(ID) From dbo_Controle"
rs.Open Query, CurrentProject.Connection
rs.MoveFirst
ID = rs.Fields(0).Value
Query = "INSERT INTO dbo_Controle VALUES (" & ID + 1 & ",'" & Me.txtControleTime & "')"
Set cn = CurrentProject.Connection
Debug.Print (Query)
cn.Execute Query

rs.Close
cn.Close
Set rs = Nothing
Set db = Nothing

This is the code I am using on different forms with different queries.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Walker
  • 1
  • 1

1 Answers1

0

I was having the same issue, and was able to fix it by using CurrentProject.AccessConnection

James
  • 1