Access 2000 connecting to SQL server questions

[Scott Simonson]

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...

[Merle Nicholson]

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******** *********@twist er.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...

[Tony Toews]

"Scott Simonson" <ss************ *****@wi.rr.com > wrote:

Unfortunatel y, 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

[Habibullah]

You could download MSDE free from Microsoft web site, it is limited
version of SQL server for personal use.

[Rich Protzel]

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.Connectio n, strSql As String
cn.Open "Provider=SQLOL EDB;Data Source=FRK53411 3;" & _
"Initial Catalog=zDonAmb Inc;User ID=sa;Password= "

strSql = "SELECT * INTO sqlSrvTable FROM " & _
"OPENROWSET('Mi crosoft.Jet.OLE DB.4.0', " & _
"'Access 9.0;Database=C: \yourDir\YourMd b.mdb', " & _
"'SELECT * FROM [AccessTable]')"

Rich

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

[Tony Toews]

"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