Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Red face Unanswered: 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!
    Andy

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I believe Access has a table import utility which allows this

    You will find it in
    Files --> Get External data --> Import
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Posts
    7

    Arrow

    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    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!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    thanks Brett,
    I'll give it go. our customer DB has about 6000 records, and i can't see re-typing all the time...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Lettuce know how it turns out;-)

    btw...6,000 isn't big...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    7
    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!

  10. #10
    Join Date
    Dec 2003
    Posts
    7
    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

  11. #11
    Join Date
    Dec 2003
    Posts
    17
    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.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2003
    Posts
    7
    gotcha Brett,
    I'll let you know how I make out...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •