Connecting Access to SQL Server

[Sticksboy]

I am totally new to ADO and I am wanting to connect an Access database to SQL Server database to bring back data tables. The SQL Server is on a Remote Desktop, and I dont want to just link the tables as that would mean I am permenantly connected to the SQL Server, I cant have this as sometimes I will need the Access database when I dont have a connection to the Remote Desktop.
I have read through some of the threads already about connecting and am totally lost, can someone write me a connection code that would bring a table from SQL Server and import it into a table in my access databse, can you also anotate it so that I can follow it through and try and understand what its doing.

I would be very grateful if this could be done.

Thanks,
Jez

[NeoPa]

Everyone's a comedian today.
This site is not a free programming resource for those unprepared to do their own code.
We can lead and help YOU to do YOUR work.

MODERATOR.

[NeoPa]

Having said that, I think an ODBC connection would probably suit you well enough as there is no penalty for having a connected or linked table which you only use when the connection is there.

[Sticksboy]

Having said that, I think an ODBC connection would probably suit you well enough as there is no penalty for having a connected or linked table which you only use when the connection is there.

If I dont have access to the Remote Connection, the linking of tables is no use, as it wont run any query that I have in my database. Thats why I have been looking at ADO as a connection to the SQL Server and once imported the tables then ending the connection, this gives me full access to the data in the tables.

[Sticksboy]

Everyone's a comedian today.
This site is not a free programming resource for those unprepared to do their own code.
We can lead and help YOU to do YOUR work.

MODERATOR.

I was wanting help rather than someone totally write it, as then I can understand what its doing.

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