how to connect ms access to sql

[marianne]

I am a new learned of ms access. How can I connect ms access to SQL ? do I need a SQL server so that I can connect access to SQL ?

[Matrix]

If you referred to 'SQL server' by 'SQL', yes, you need a SQL server. Check out the following article: Ways to work with SQL Server data in Access.

[marianne]

what I mean about sql is sql tables. matrix I also viewed your link but I did not get enough information, can you give me an example how to connect to sql table using access please?

[Matrix]

Setup an ODBC data source first, refer to this tutorial. Actually most databases can be configured as ODBC data source. Then in Access, you can open an ODBC data source directly from 'File->Open', set 'Files of Type' as ODBC Database.

[marianne]

you mean I actually go to control panel to setup odbc?

[RuralGuy]

Hi Marianne,
I understand you are still struggling with this. I need to ask some questions first.
1) What version of Access are you using and what Operating System?
2) What is your background? Have you coded before?
3) Do you have SQL Server?
4) Do you know what ODBC means?

[marianne]

yes I am struggling. I am under winxp, ms access 2000. I know how to code but not that good. I have my sql server 2000 developer edition installer but did not installed it coz I was hoping I could connect ms access or link it to sql tables without sql server. I dont know what ODBC means rural guy.

[RuralGuy]

I'm by no means competent on SQL Server but willing to give it a go. I've got MSDE on this machine and a complete Database and can try to get things going for you. I doubt you can access the SQL database without the Server running since that is the engine that knows how to run the db. Then you will need to get an ODBC driver to talk to the SQL Server. It is either already on your system or can be downloaded from MS.

[marianne]

I see. so may I ask if there is a type of database with access can connect directly?

[RuralGuy]

When you go to File>Get External Data>Link Tables... down at the bottom of the dialog for selecting a db in the Files Of Type: control, the last selection on mine is ODBC Databases (). Selecting that brings up another dialog box. If SQL Server were running, I believe we would have some selections in there. As it is, right now it is blank.

[marianne]

I read an article and here it is:

Comment by Tom Miller
August 23, 2007 @ 9:17 am
You can also link SQL Tables into an Access database without a DSN by using a little bit of VBA code and DAO (yes, good old DAO):

Public Function AttachTables()
Dim tdTemp As TableDef
Set tdTemp = CurrentDb.CreateTableDef("TableName")
tdTemp.SourceTableName = "dbo.TableName"
tdTemp.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Tr usted_Connection"
' without Trusted security, you can pass a user id and password instead:
tdTemp.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;ui d=UserID;pwd=PassWord"
CurrentDb.TableDefs.Append tdTemp
End Function

WHAT IS YOUR COMMENT ON THIS RURAL GUY? IS THIS A WAY TO CONNECT TO SQL TABLES WITHOUT SQL SERVER?

[RuralGuy]

I have to ask what the big plan is? What is wrong with Jet as a BackEnd?

[RuralGuy]

WHAT IS YOUR COMMENT ON THIS RURAL GUY? IS THIS A WAY TO CONNECT TO SQL TABLES WITHOUT SQL SERVER?

It just means you can go around the DSN but you still use the ODBC driver and only SQL Server knows how to retrieve the records.

[marianne]

I wanted my database to be big. because I read an article that ms access is just small.

[marianne]

so does that mean that, on that code, sql server does not exist? but still able to connect to sql tables?