467,225 Members | 1,364 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,225 developers. It's quick & easy.

Connecting Access to SQL Server

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
Jul 1 '07 #1
  • viewed: 1693
Share:
4 Replies
NeoPa
Expert Mod 16PB
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.
Jul 2 '07 #2
NeoPa
Expert Mod 16PB
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.
Jul 2 '07 #3
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.
Jul 3 '07 #4
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
Jul 3 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by kamilla | last post: by
2 posts views Thread by news@celticbear.com | last post: by
1 post views Thread by Dishaa V | last post: by
2 posts views Thread by Patrick F | last post: by
reply views Thread by Adict | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.