Connecting to SQL Server via ADO

[Jez]

This below is my code that have written, but I keep getting an error message
on the line highlighted with stars.

The error is - 3704 - Operation is not allowed when the object is closed.
what does this mean?

Private Sub cmdImport_Click()
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim Comm As ADODB.Command

Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset

Dim strConn As String
Dim strInsert As String
Dim strSelect As String

strConn = "Provider=sqloledb;Data Source=SLIDER\BISR;Initial" & _
"Catalog=NameofSQLServer;User Id=myunsername;Password=mypassword"

strSelect = "SELECT * FROM dbo.servicing_jobs"

********Set objRS = objConn.Execute(strSelect)*******

Set Conn = New ADODB.Connection
Set RS = New ADODB.Recordset
Set Comm = New ADODB.Command

Conn.Open strConn
Comm.ActiveConnection = Conn
Comm.CommandType = adCmdText

Do While Not objRS.EOF
strInsert = "INSERT INTO tblServicing"

Comm.CommandText = strInsert
Comm.Execute

objRS.MoveNext
Loop

UpdateAwardErr:
If Err.Number <> 0 Then
MsgBox Err.Number & Err.Description
Exit Sub
Else
MsgBox "Records were successfully inserted", vbInformation, "ServicingJobs
Update"
Exit Sub
End If
End Sub

 

[BeWyched]

Hi

What's dbo.servicing_jobs in the previous line? It looks like an unset
object perhaps?

I suspect that's where your problem lies.

BW

 

[Jez]

BW, I dont know what you mean by unset object.

dbo.servicing_jobs is the table in the SQL Server database, its what I want
to select all from and then load into tblServiceJobs in my Access database.

Jez

 

[Douglas J. Steele]

You have to instantiate a Connection object and then set the
ActiveConnection property of objRS to that connection. There's no reason to
have two connection objects, so you may as well use Conn once you've defined
it.

Private Sub cmdImport_Click()
Dim Conn As ADODB.Connection
Dim Comm As ADODB.Command
Dim objRS As ADODB.Recordset

Dim strConn As String
Dim strInsert As String
Dim strSelect As String

strConn = "Provider=sqloledb;Data Source=SLIDER\BISR;Initial" & _
"Catalog=NameofSQLServer;User Id=myunsername;Password=mypassword"

strSelect = "SELECT * FROM dbo.servicing_jobs"

Set Conn = New ADODB.Connection
Set objRS = New ADODB.Recordset
Set Comm = New ADODB.Command

Conn.Open strConn

Set objRS.ActiveConnection = Conn
Set objRS = objConn.Execute(strSelect)

Of course, your Comm.Execute statement is going to fail, since "INSERT INTO
tblServicing" isn't a valid SQL statement.

 

[Jez]

Douglas, Thanks for that. I understand what you mean. So in terms on the
inserting into tblServicingJobs, whats the correct way of doing this, other
than the INSERT INTO which I used first?

Also if I was to import more than 1 table accross from the SQL Server, how
would I do this, would I repeat the code again or is there a simpler way?

I thought about building a table in my access databse which could hold the
names of the SQL Server tables I want to import and names of the Access
tables where I want to import to, is this a good idea?

Jez


Jez

 

[Douglas J. Steele]

Easiest approach would be to have a table linked to dbo.servicing_jobs, and
then create an Append query based on that linked table that appends to
tblServicingJobs. However, is there a reason why you can't just link to the
SQL table, rather than making a copy of it?

 

[Jez]

Doug,
The reason for wanting to do it this way is that I dont always have a
connection to the SQL Server, I only have this connection when at home, when
I am in different offices working, I may not be able to connect (long story).
So I wanted to be able to bring all the relevant tables accross that I would
need every morning and then I can continue as normal wherever I am that day.
I thought about the linking tables and then appending to my tables at first
but then thought how messy that would be as I would have 2 tables for
everything, 1 linked table and 1 my table.

Jez

 

[Douglas J. Steele]

Well, you could always create pass-through queries that get the data from
SQL Server, and then use those pass-through queries instead of tables in
your Append queries.