December 8, 2016 at 7:10 am
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? Thanks!!
December 8, 2016 at 10:27 am
Hi
This is a SQL server forum, so you can expect quick help for SQL questions.
Yet, looking at your code I think you need to rearrange your code, you can use
Do Until rs.EOF
only after
Set rs = cmd.Execute
December 8, 2016 at 3:23 pm
Just a couple thoughts... I don't use ADO quite the way you do, in terms of how to write the ADO elements, but I'm wondering why not just convert the data up into SQL Server using the appropriate version of Microsoft's SSMA tool (SQL Server Migration Assistant). There's a different versions for quite a large number of combinations of SQL Server version and MS Access version. Then you can get away from corruption issues within the Access .MDB file.
Also, once you do something like that, all the Access tables become linked tables to the server that hosts the data, so then you don't need to play backup, and can instead rely on the SQL Server backup / restore capability.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply