can this be done?

We have a large customer database in MSSQL 2000, and we are looking at scheduling software that uses MS-Access for the DB. is there an easy way to take the customer data from our AR_CUST table in SQL and link/update/insert as into Access? I've seen all kinds of stuff about going from Access to SQL, btu not the other way. HELP!
AndyI believe Access has a table import utility which allows this

You will find it in
Files --> Get External data --> Import|||yup,
I see that. in order to use it though, we would have to import every time we add a customer before we could schedule them. I was looking for more of an ODBC/dynamic type thing.|||Don't import in to Access!

Perform an Link to SQL Server from Access...using Code or File>GetExternalData>Link

If you do the later you'll need to set up an ODBC Connection (Code is better)

DONT USE sa as your login|||HEY BRETT,
I've done the link thing, and the SQL table appears in the DB for the new software. HOWEVER, the DB already has a table (Customers) and the SQL table is AR_CUST. The field names don't match, and I can't seem to find a way to get the data from SQL INTO the customer table!|||The "application" has data tables already?

Ohh I get it...

1st make a backup copy of the application, and put it away somewhere safe..

How big is the app btw?

This may be tedious..actually I wrote code to do this...

But create what I call Alias Views for the table...

Queries and tables in Access are referenced the same way...

Take your AR_CUST, and rename it with an "X_" in front...

The create abn access query called AR_CUST..use the sql server customer table as the data source

Now map the fields...make sure that every column in the AR_CUST table is there...even if you don't have that field in sql server..

AND make sure the names are identical to AR_CUST

for example if SQL Sever has LName and AR_CUST is Last_Name

Map it in the QBE Grid as Last_Name: LNAME

Do that for every column in the table.

Now do it for every table

This way, you've "Faked" the code out...it'll never know it's talking to sql server...

Now, this is hookey at best...AND if the code/forms are written poorly, you'll notice a performance hit...

For example, if they have a dozen drop downs on a form, and they all talk to the backend, it'll open a connection per...if they're upodateable, it'll be 2 per...

GOOD LUCK|||thanks Brett,
I'll give it go. our customer DB has about 6000 records, and i can't see re-typing all the time...|||Lettuce know how it turns out;-)

btw...6,000 isn't big...|||No,
6000 isn't very big. There is a large amount of admin related to this business, tho, and trying to keep the office staff updating the schedule software will never work. I'll let you no!|||Ok,
started on this, and I'm a little confused. In SQL the table is AR_CUST.
in Access the table is 'Customers'. Now, I need to rename the the Access table 'Customers' to something like 'Customersx' or 'XCustomers'; anything but 'Customers', right? Then in Access create a new Query using the AR_CUST table in SQL as the data source named 'Customers'. That is how far I've got. Now how do I map the query to work correctly? Do I need to map it to a new 'Customers' table? I can't seem to find an area in Access to define the mapping! Talk about feeling like a DooF!
Andy|||If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.

1. If the data structure of the Customers table is the same in both the current Access database and the SQL Server database then you can archive the current Access database. Create a new copy of the Access database and remove the Access Customers table, then create a linked table to your AR_CUST table in SQL Server and rename the linked table in Access to Customers

2. If the data structure of the Customers table in Access is different to the data structure in SQL Server then create a view in SQL Server renaming all the columns to the correct Access column names. Then create a linked table within Access to the view and rename the view to Customers.

Using the above methods means that you won't need to change any code or forms in Access, unless the Access database is using ADO to access a Customers table somewhere, but this seems unlikely as it appears that the Access database is a stand-alone database at the moment.

If you do use ADO you will either have to change the Access forms, queries and everything to use AR_CUST or change SQL Server to use a table called Customers.|||Originally posted by sjp
If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.


No. You don't want to store the data in Access.

1. You want the Query to be named AR_CUST
2. You want to pick the linked SQL Server Table Customer as the Datasource
3. You want to make sure you have every column that exists in the AR_CUST Table in the query, AND Named exactly as the way they are in AR_CUST
4. You need to establish a mapping for every column, like, for example the customers last name..

The SQL would look Like

If the Customer table has the field called LName and AR_CUST is defined as Last_Name

SELECT LName AS Last_Name FROM dbo_Customer

In the QBE Grid it would look like

Last_Name: LName

You need to make sure that you have every single column that AR_Cust has. If not you'll have problems...|||gotcha Brett,
I'll let you know how I make out...

Can SQL2000 & SQL2005 co-exist on the one server?

i realise you can have both SQL2000 & SQL2005 Client software co-existing on one (developement) machine.

but i cant seem to find a definitive answer on whether i can install both complete databases on a server?

we are currently building a new development server and would like to install both version on it if possible?

Cheers,
Craig

You can install both. You'd have to have an instance name, however.

ie. servername\sql2005

I think it's less confusing if you put both servers on separate instances..

instead of

sql2000: servername
sql2005: servername\sql2005

use

sql2000: servername\sql2000
sql2005: servername\sql2005

this way you know what environment you're pointing to..

Can Reporting Services software be downloaded?

How do I get the Reporting Services software? Can it be downloaded from a
Microsoft site?
Thanks
RonNevermind - turn out asp.net wasn't installed on the server. Once it was,
the server components showed up upon install.
"Ron" wrote:
> How do I get the Reporting Services software? Can it be downloaded from a
> Microsoft site?
> Thanks
> Ron

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?
aspirin,
Unlikely unless you are using WMI to query hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?aspirin,
Unlikely unless you are using WMI to query hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?

Can replacing nic in server cause null fields?

Our IBM motherboard died and had to be replaced today.
Now our imaging software returns null fields in some data
fields. We run SQL 2000 on NT 4.0 server on this box.
Could somehow the replacement of the motherboard that uses
a different mac burned onto the integrated NIC controller
have caused this?aspirin,
Unlikely unless you are using WMI to query hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aspirin wrote:
> Our IBM motherboard died and had to be replaced today.
> Now our imaging software returns null fields in some data
> fields. We run SQL 2000 on NT 4.0 server on this box.
> Could somehow the replacement of the motherboard that uses
> a different mac burned onto the integrated NIC controller
> have caused this?

can Password be hacked?

Can password be hacked? Because my friend ever say, there is a software can
hack SQL Server. Is it true? If yes, how to avoid from hacker? and What name
program that can do it?
which is more secure, use login with "Windows authentication" or "SQL
authentication"?Hi
Any server may be open to a brute force attack, and therefore ensuring
strong passwords is essential. Auditing login failures therefore important.
The main problem in the past has been that passwords have not been mandatory
,
therefore making it easy for someone to obtain access.
Windows authentication is more secure.
This may help:
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
John
"Bpk. Adi Wira Kusuma" wrote:

> Can password be hacked? Because my friend ever say, there is a software ca
n
> hack SQL Server. Is it true? If yes, how to avoid from hacker? and What na
me
> program that can do it?
> which is more secure, use login with "Windows authentication" or "SQL
> authentication"?
>
>|||Windows Authentication is recognized as the more secure option and is
recommended wherever possible. It's as secure as Windows is but there's no
such thing as absolute security.
David Portas
SQL Server MVP
--|||> Can password be hacked? Because my friend ever say, there is a software can
> hack SQL Server. Is it true? If yes, how to avoid from hacker? and What na
me
> program that can do it?
In theory, all passwords of any kind can be hacked. The real question "Can t
he
password be hacked in a timely fashion such that it is still useful?" What m
akes
SQL users weaker as a security structure than Windows Authentication has not
hing
to do with the encryption scheme. It has to do with other featuers like
disabling passwords after a specified number of attempts, requiring password
periodic password changes, logging failed password attempts and most
importantly, not sending the username and password in the clear to authentic
ate.
Some of these issues have been addressed in SQL 2005.

> which is more secure, use login with "Windows authentication" or "SQL
> authentication"?
Windows authentication is far more secure than SQL authentication.
Thomas