Receive MSSQL Data in Access

[bloedgever]

I m trying to make a connection in vba with an mssql server.
I need some data from an mssql server, but most of the data will come from access itself. I need to do this with a coded query because the mssql db is too big to import in access

can anyone help me here plz? tnx

[RobDog888]

If you are already connecting to Access to get some of the data then you could just Link the SQL Tables needed.
Linked tables do not store the table data in Access, more of just a "link". You can open the Table in Access and
view the data in the SQL Table.

[bloedgever]

tnx

but isn t this possible just with code? something like

dim rst 'as some kind of recordset

Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1" _
& ";User ID=" _
& ";Password=" _
& ";Initial Dialog=" _
& ";Data Source="

Query = "select * from book"

Set rst = New ADODB.Recordset
rst.Open Query, cnn

rst.Next
MsgBox (rst(0))
Loop

i tried it this way , but i keep getting errors

[RobDog888]

Yes it is but you need to supply your username and password etc. SQL Server requires a valid logon not like
Access where you can just open the db.

'connectionstring:
Provider=sqloledb;Data Source=ServerName;Initial Catalog=pubs;User Id=sa;Password=asdasd;"