By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,900 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,900 IT Pros & Developers. It's quick & easy.

Access 2000 connecting to SQL server questions

P: n/a
I have a client that has asked me to modify an existing Access MDB. My
question is they want me to populate their SQL server(2000 I believe). How
can I accomplish this in Access 2000? Are there things I should be aware of
first?

Another question, where can I get a copy of SQL server to test with? Isn't
there a "personal" version I can attain for development purposes? It seems
ridiculous for me to have to "buy" SQL server for testing purposes.
Especially since no more than one user will access it, me.

Thanks in advance...
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
The SQL Server will import tables from Access. The "personal" version of SQL
Server is included in Office XP Developer version. I don't know about the
Office 2000 versions. Also, if you want, you can create tables in SQL by
using Access' SQL, and move data the same way by various ways, including
using VBA and also through Access queries.
"Scott Simonson" <ss*****************@wi.rr.com> wrote in message
news:EL*****************@twister.rdc-kc.rr.com...
I have a client that has asked me to modify an existing Access MDB. My
question is they want me to populate their SQL server(2000 I believe). How
can I accomplish this in Access 2000? Are there things I should be aware of first?

Another question, where can I get a copy of SQL server to test with? Isn't
there a "personal" version I can attain for development purposes? It seems
ridiculous for me to have to "buy" SQL server for testing purposes.
Especially since no more than one user will access it, me.

Thanks in advance...

Nov 12 '05 #2

P: n/a
"Scott Simonson" <ss*****************@wi.rr.com> wrote:
Unfortunately, I don't have developer versions anywhere. What would I look
for on the CDs?


You can also now purchase SQL Server Developer Edition for $50 US.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #3

P: n/a
You could download MSDE free from Microsoft web site, it is limited
version of SQL server for personal use.
Nov 12 '05 #4

P: n/a
The easiest way to pass data from Access to sql server is to use DTS
from sql Server. DTS is basically a data import/export wizard. It is
listed in the windows start menu in the Microsoft Sql Server menu, or if
you have access to the Enterprise Manager you can right click on the
Databases icon, or the Tables icon for the respective sql database and
invoke DTS that way.

If you don't have access to Enterprise Manager (or the sql Server
computer) you can use ADO. You could do it row by row with recordset
objects or you could use the OpenRowset Method (much faster):

Dim cn As New ADODB.Connection, strSql As String
cn.Open "Provider=SQLOLEDB;Data Source=FRK534113;" & _
"Initial Catalog=zDonAmbInc;User ID=sa;Password="

strSql = "SELECT * INTO sqlSrvTable FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Access 9.0;Database=C:\yourDir\YourMdb.mdb', " & _
"'SELECT * FROM [AccessTable]')"

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
"Scott Simonson" <ss*****************@wi.rr.com> wrote:
I actually saw that. But am confused on what limitations there are. By
limitations, I don't mean number of users. What features are included with
"Developer Edition"? Does it have to run locally? Could I try to connect to
it from another PC?


I have no idea. I'd suggest checking MSs website. I suspect its similar to the MSDE
version in that a limited number of connections and a maximum size of 2 gb per
database.

Nope, I'm wrong.

http://www.microsoft.com/sql/evaluat...ew/default.asp
"special development and test end-user license agreement (EULA) that prohibits
production deployment. "

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.