Access Database ADODB Connection to SQL Server Table

[BennyLinton]

Thanks in advance for any help here... I have an Access database that I'm trying to move the contents of one of its tables to a table in SQL Server each time the Access database is closed. In VBA I have set a connection string constant in an Access Module:

Option Compare Database

Public Const CONN_STRING As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ACME_Search;Data Source=Server67"

In the Access close_form my VBA is trying to create a recordset by iterating through all the records then writing them to the SQL Server table via a parameterized Stored Procedure:

Private Sub Form_Close()

Dim con As adodb.Connection
Dim cmd As adodb.Command
Dim rs As adodb.Recordset

Set con = New adodb.Connection
con.ConnectionString = CONN_STRING
'Debug.Print CONN_STRING
con.Open

Set cmd = New adodb.Command

cmd.ActiveConnection = con

Do Until rs.EOF
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT A.FirstName, A.LastName, A.City, A.State/Province, A.PostalCode, A.ID From applicants A"
Set rs = cmd.Execute

If Not rs.BOF And Not rs.EOF Then
Dim cmd2 As adodb.Command
Set cmd2 = New adodb.Command
cmd2.ActiveConnection = con
cmd2.CommandType = adCmdStoredProc
cmd2.CommandText = "insertAcme_China"

cmd2.Parameters.Append cmd.CreateParameter("@FirstName", adVarChar, adParamInput, 40, rs(0))
cmd2.Parameters.Append cmd.CreateParameter("@LastName", adVarChar, adParamInput, 40, rs(0))
cmd2.Parameters.Append cmd.CreateParameter("@City", adVarChar, adParamInput, 40, rs(1))
cmd2.Parameters.Append cmd.CreateParameter("@State", adVarChar, adParamInput, 40, rs(2))
cmd2.Parameters.Append cmd.CreateParameter("@PostalCode", adVarChar, adParamInput, 40, rs(3))
cmd2.Parameters.Append cmd.CreateParameter("@OriginalDatabaseId", adVarChar, adParamInput, 40, rs(4))

cmd2.Execute

End If
rs.MoveNext
Loop

End Sub

Finally my SQL Server SP is as follows:

USE [ACME_Search]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[insertACME_China]
(
@FirstName varchar(150),
@LastName varchar(150),
@City varchar(150),
@State varchar(70),
@PostalCode varchar(10),
@OriginalDatabaseId varchar(150)
)

As

Insert into Acme_China_Import (FirstName, LastName, City, State, PostalCode, Country, Certifications, OriginalDatabase, OriginalDatabaseId, RegionID, InsertDate)
Values (@FirstName, @LastName, @City, @State, @PostalCode, 'China', 'Acme', 'Acme', @OriginalDatabaseId, 6, GetDate())

My initial error is coming up as: "Runtime error '91' Object variable or With block not set" with the debugger pointing to the line "Do Until rs.EOF"

But I'm probably missing something else too... any ideas? Should I be using DAO? Thanks!!

 

[Minty]

Why not link to the table and do an Insert / Update query ?

 

[Ranman256]

Using code to do this is extremely slow.
Link in the SQl tables and run a query for instant results.
No code needed.

 

[BennyLinton]

I was initially trying to do a new ODBC connection but I get the error:

"You are logged on with non-Administrative privileges. System DSNs could not be created or modified."

 

[Minty]

Make sure you run the ODBC set up as administrator.

 

[BennyLinton]

How do I open the Access database with Admin privileges?

 

[pbaldy]

For the record, your error is due to the fact that you haven't opened a recordset at the point that line runs. I would certainly used the suggested query if possible though.

 

[Minty]

Set up the DSN using the system odbc tool - if you are using 32-Bit Access then you need to use the 32-Bit ODBC which is probably in SysWOW folder.

 

[BennyLinton]

I used the odbcad32 application to set up the new DSN but when I try to use the external tools in Access it lists the DSN as a USER Type which will not be usable by others on the network correct?

 

[pbaldy]

When I used a DSN (I use DSN-less connection now), I created a File DSN on the server and used that to link tables. If you use a User or System DSN, it will only work on that computer, so you'd have to set one up on each computer. The File DSN doesn't require setup on each computer.

 

[BennyLinton]

When I used a DSN (I use DSN-less connection now), I created a File DSN on the server and used that to link tables. If you use a User or System DSN, it will only work on that computer, so you'd have to set one up on each computer. The File DSN doesn't require setup on each computer.

I'm not sure how to create a File DSN on our network's server which is why I was wanting to get away from ODBC and go DSN-Less.

 

[BennyLinton]

I have changed some code and it is now getting to the line:

cmd.CommandText = "SELECT A.FirstName, A.LastName, A.City, A.[State/Province], A.PostalCode, A.ID From [applicants] A"

But it says it doesn't recognize the table: "applicants" - I checked the spelling of the Access table.

It is a linked table from an Access backend.

 

[pbaldy]

You can still have linked tables with DSN-less connection:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

https://support.microsoft.com/en-us/kb/892490

 

[pbaldy]

I have changed some code and it is now getting to the line:

cmd.CommandText = "SELECT A.FirstName, A.LastName, A.City, A.[State/Province], A.PostalCode, A.ID From [applicants] A"

But it says it doesn't recognize the table: "applicants" - I checked the spelling of the Access table.

It is a linked table from an Access backend.

If you're still passing that to SQL Server, it would have no knowledge of the Access table. What's been suggested is linking to the SQL Server table in Access, then using an Access query which will then be able to "see" both.

 

[BennyLinton]

You can still have linked tables with DSN-less connection:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

https://support.microsoft.com/en-us/kb/892490

I placed the following code in my Functions module:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Then I set it to call upon opening my main form:

Private Sub Form_Open(Cancel As Integer)
If AttachDSNLessTable("ApplicantsCopy", "China_Import", "Server67", "ACME_Search", "", "") Then
MsgBox "SQL Server table successfully attached."
Else
MsgBox "SQL Server table failed to attach."
End If
End Sub

When the form opens I get this error: "Compile error: User-defined type not defined" and the debugger points to this line: Dim td As TableDef

 

[pbaldy]

I suspect it has to be disambiguated:

Dim td As DAO.TableDef

 

[BennyLinton]

I suspect it has to be disambiguated:

Dim td As DAO.TableDef

I'm getting the same error

Maybe I'm not contructing the On_Open line correctly