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.Connectio n
Dim RS As ADODB.Recordset
Dim Comm As ADODB.Command

Dim objConn As New ADODB.Connectio n
Dim objRS As New ADODB.Recordset

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

strConn = "Provider=sqlol edb;Data Source=SLIDER\B ISR;Initial" & _
"Catalog=Nameof SQLServer;User Id=myunsername; Password=mypass word"

strSelect = "SELECT * FROM dbo.servicing_j obs"

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

Set Conn = New ADODB.Connectio n
Set RS = New ADODB.Recordset
Set Comm = New ADODB.Command

Conn.Open strConn
Comm.ActiveConn ection = Conn
Comm.CommandTyp e = adCmdText

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

Comm.CommandTex t = 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, "ServicingJ obs Update"
Exit Sub
End If
End Sub