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 the TEMPDB be backed up?

You need to run a "Full Backup" on all of your other
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.

> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Can the TEMPDB be backed up?

You need to run a "Full Backup" on all of your other
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>
> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.

> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/