MS Access and Sql Server

[d9pierce]

Hello all,
I am new to SQL server and am having some problems with Access and
connecting to it. I have read so many articles but have found not one
that spells it out in pure english! Hoping someone can help?

OK, I just built a new machine, bought all sorts of software, and now
I am ready to go. I have previously been just using MS Access 2003 /
2007 but now I want to store my tables and quarys in SQL Server. I
have SQL server 2008 and 2005, I also have visual studio 2008 along
with my MS access 2003 / 2007. So far I cannot get a file to connect
to my SQL Server. How on earth does one do this. It appers simple but
I am missing some tech data in this area I guess.

Some general questions first:
Do I need to set up my home network first? I am basically using my new
machine to work off of and will have some remote computers also
configured through a network soon, I just havent done it yet. didnt
want to get ahead of my self.
It asked me to create a DNS and I have no idea how to do this or
access it once I have done this. This seams to be asked weather I am
in Access 2003 or 2007 when I try to connect to SQL Server 2008. Now I
can migrate files into SQL server just fine but when I am in access, i
cannot conect to the server. I just dont understand this.

What I want to do is start a new project in Access 2003 or 2007,
doesnt matter at this point, and creat my tables/Quary in SQL Server
but I need some help on how to get the two connected? I would also
like to import some of my tables possible from an existing Access
Project I have stored but I can re create if necessary, no big deal,
just more typing.

Could someone please give me the plain english version of how to
accomplish starting a new MS project and connecting to an SQL Server
2008 or 2005 if neecessary. I would be so greatful and have more hair,
LOL!

Thanks to all!
(e-mail address removed)

 

[Dorian]

My advise is to hire someone to do this for you. You obviously don't have the
expertise to set up something of this complexity. And even if you somehow
manage to do it, the first time it 'breaks' you will be unable to fix it. You
could possibly lose data that is vital to your business.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Paul Shapiro]

Not clear if SQL Server is running on the same machine as Access? In any
case, recent SQL Server versions default to disabling all network access.
You need to enable TCP/IP connections in the SQL Configuration Manager. If
you're connecting between different computers, the firewall on the computer
running SQL Server needs to allow inbound connections to tcp port 1433 (the
SQL Server connection port). With the default of Windows authentication for
SQL Server, the account being used on the machine which is connecting to the
SQL Server machine must exist with the same username and password on the SQL
Server machine, and must have been granted access to login and to access the
desired database. If you're using SQL Server authentication, the connection
has to specify a sql server username and password that has access to the
desired database.

If you want to test connectivity, you can create an Access Project (.adp) to
connect to the SQL Server. Once that's working you can use the DSN
connection.

If you're new to SQL Server, getting an introductory book would probably be
a good start. There's more to learn once you get the connectivity
established.

 

[d9pierce]

Hi and thanks so much,
I did have all tcp/ip enabled and I think I was able to open this tcp
port 1433 in windows fire wall. I went through some reading and was
able to go through and add an exception which should have done the
trick but didnt evidently? I cannot find any down to earth ways to
insure this port 1433 is open. In any event, I tried another project
in Access, Server 2008 is on same machine and using windows auth, name
and password are correct, and now when I hit the SQL button in access
2007, it just freezes and goes into not responding. I have rebooted,
opened several new and existing projects and still just hangs up. I
agree with you and books as I have ordered a few all ready, gone on
line for tutorials, and downloaded the msdn that came with my 2008 and
installed. I have not found anything specific on setting this up so it
will connect to my database as of yet. Well, there is a lot of info to
get your tables to SQL and that works well, but if you cant connect
the database, then its pretty useless? So far I have not been able to
connect one db to this sql server, new or existing? Is ther a book or
tutorial in the system available for setting this up and connecting
access to the SQL server? If not, if we ever figure this out I think I
will make one, LOL!
Thanks again,
Dave

 

[Gina Whipp]

d9pierce,

Let's start from the beginning as it appears you are new to this...

1. What Windows versio do you have?
2. When you go to Enterprise Manager are you able to open the sample
tables?
3. When you upsize are you using the Wizard in Access?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[d9pierce]

d9pierce,

Let's start from the beginning as it appears you are new to this...

1.  What Windows versio do you have?
2.  When you go to Enterprise Manager are you able to open the sample
tables?
3.  When you upsize are you using the Wizard in Access?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi All,
I am running Windows Vista Ultimate x64
I can access the sample tables
and I am using upsize wizzard in access 2007, however, I have used
imigration from sql 2008 and that works well but I still cannot
connect to my data base in access?
Just so mind blowing with this!
Thanks
Dave

 

[Gina Whipp]

Dave,

Once you use the Upsizing Wizard do you see the links in your database? If
yes, can you open the tables? If you cannot what is the message?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

d9pierce,

Let's start from the beginning as it appears you are new to this...

1. What Windows versio do you have?
2. When you go to Enterprise Manager are you able to open the sample
tables?
3. When you upsize are you using the Wizard in Access?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi All,
I am running Windows Vista Ultimate x64
I can access the sample tables
and I am using upsize wizzard in access 2007, however, I have used
imigration from sql 2008 and that works well but I still cannot
connect to my data base in access?
Just so mind blowing with this!
Thanks
Dave

 

[d9pierce]

Dave,

Once you use the Upsizing Wizard do you see the links in your database?  If
yes, can you open the tables?  If you cannot what is the message?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi,
OK, I can use the upsize in access but when I creat a new DB, it goes
to my server which I have to type in as the drop down doesnt list any
servers, then I hit OK and an error message pops up stating that "
the upsizeing wizzard does not work with this version of SQL server,
contact MS for the latest info and updates. Now this is the same
message I get with sql 2005 too! Now, if I go to existing DB, then I
have to find a file data source and a DNS and I dont know how to do
this so I have not gotten past this part.

 

[Gina Whipp]

Dave,

You do realize the SQL Server needs to be running when you do this? Is it
running?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Dave,

Once you use the Upsizing Wizard do you see the links in your database? If
yes, can you open the tables? If you cannot what is the message?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi,
OK, I can use the upsize in access but when I creat a new DB, it goes
to my server which I have to type in as the drop down doesnt list any
servers, then I hit OK and an error message pops up stating that "
the upsizeing wizzard does not work with this version of SQL server,
contact MS for the latest info and updates. Now this is the same
message I get with sql 2005 too! Now, if I go to existing DB, then I
have to find a file data source and a DNS and I dont know how to do
this so I have not gotten past this part.