Connection to SQL Server

  • Thread starter Thread starter Joop
  • Start date Start date
J

Joop

In the past 2 years I developed an application that by now is growing a bit
big, several tables of over 500.000 records and a lot of smaller ones.
So I decided to migrate to MS SQL Server.

I imported the tables in SQL server, deleted all tables in Access and
created linked tables in Access.

In every procedure where I use ADODB in my code I get errors.
I tried different connection strings to connect to the SQL Servers tables
straight away. I also tried to connect using the same DSN I used to link the
tables.

Who can help me to determine the right connection string? (or another
solution)

A sample of the code where it goes wrong, the first open (line 13) runs ok
but it does not execute line 24 so probably I have permission to read but
not to delete or write?


1 Private Sub CmdBereken_Click()
2 Dim cn As ADODB.Connection
3 Dim periode As String
4 Dim MemPeriode As String
5 Dim AantalRecs As Long
6 Dim ArtGroep As String
7 Dim SQLString As String
8 Dim QryStart As Single, QryEnd As Single, QryEet As Single
9 Dim rcdB As New ADODB.Recordset
10 Dim rcdI As New ADODB.Recordset
11 Dim rcdP As New ADODB.Recordset

12 Set cn = CurrentProject.Connection
13 rcdP.Open "SELECT * FROM TblBonusPeriode", cn, adOpenKeyset,
adLockOptimistic
14 MemPeriode = rcdP!FldBonusPeriode
15 rcdP.Close
16 If MemPeriode = Me!TxtPeriode Then
17 periode = Left$(TxtPeriode, 4) + Right$(TxtPeriode, 2)
18 'verwijderen bonus regels voor desbetreffende periode
19 QryStart = Timer
20 Screen.MousePointer = 11
21 DoCmd.SetWarnings False
22 TxtInfo = "Deleten bonussen voor deze periode"
23 Me.Repaint
24 SQLString = "DELETE * From bonus Where bonus.periode = '" &
periode & "';"
25 rcdB.Open SQLString, cn, adOpenKeyset, adLockOptimistic


regards, Joop
 
Delete statements don't generate recordsets, so it's inappropriate to use a
Recordset object when trying to run a delete statement.

Try using a Command object instead:

Dim cmdDelete As ADODB.Command

.....

Set cmdDelete = New ADODB.Command
Set cmdDelete.ActiveConnection = cn
cmdDelete.CommandText = SQLString
cmdDelete.Execute
.....

To learn more about connection strings, check out Carl Prothman's site
http://www.able-consulting.com/ado_conn.htm
 
I tried your suggestion but the resuly is stil the same (error -2147467259
(80004005))

Joop
 
Unfortunately, that's a generic error that doesn't tell much.

What's the data type of the field periode in your bonus table? Your code
will only work if it's a text field. Do you have a primary key defined for
the table?
 
Periode is a text field. The table has no primary key defined.
I tried to delete records direct in the tables. This works well with a table
that has a primary key and it does not work on , for instance, the table
Bonus. So is the primary key key the problem?
 
I created a primary key for the table Bonus and refreshed the linked tables.
Now it works ok. Why does SQL Server need an unique key to delete records?

regards Joop
 
That's just the way it is.

Actually, it's not 100% essential that the SQL Server table have a primary
key: when you create a linked table in Access, it'll prompt you to define a
primary key if one doesn't exist in SQL Server. If you supply one at that
point, you should be able to update from Access still.
 
A SQL Server MVP tells me that this behaviour is not confined to SQL Server,
but is common to ODBC data sources.

Apparently, behind the scenes, the server executes a query that looks
something like "UPDATE <or DELETE> ... WHERE SomeField = SomeUniqueValue

Without a unique index or constraint, the necessary query can not be
constructed, and the server has no way to determine which record you want to
delete or update. Rather than run the risk of deleting or updating the wrong
record, it will treat the table as read-only.
 
Back
Top