Results 1 to 3 of 3
  1. #1
    amkweb is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    4

    Problem converting ADP Access file to MDB

    Hi,
    Thanks for your help in advance.
    I have inherited an Access 2003 ADP and need to convert it to Access 2010 MDB. It's become a huge headache for the past several weeks.
    The backend is MS-SQL 2008 R2. (I am a SQL developer & have built 1000s of SPs and databases also a good .NET C# developer).
    I Would like to use DAO if possible (unless someone says ADO is better).

    I can't get Access to use:
    1. SPs that have parameters. (have got some to work)
    2. SPs with parameters that return data (to be used to fill out Access form/subform fields)
    3. SPs with parameters that don't return data.
    4. Queries that call SPs with parameters (may or may not return data)

    Do I even need to use queries?
    Can I use queries that call SPs (with parameters) and return data to Access or should I?

    example: The following code worked in ADP:

    Me.RecordSource= "execute SPNAME " & Param1 &"," & param2

    But doesn't work in MDB. How would I convert that to MDB?



    If I create a table in my SP (using select into), how can I use that table to populate my access form fields?
    (i.e. how would I define access form fields (in UI) to later dynamically use data from the table (created in SP dynamically)

    AND I would like to use DAO and NOT ADO (if possible) based on recommendations I have found online.

    Could you please refer me to a good site with concrete examples?
    Do you suggest that I use queries?
    My biggest problem is with SPs that have parameters and return data.

    Thank you so much.

    Mike
    Last edited by amkweb; 09-28-2016 at 08:50 PM. Reason: needed more clarification

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with regards converting an adp to access 2010, see this link which may help

    https://social.msdn.microsoft.com/Fo...orum=accessdev

    with regards stored procedures, see this link

    http://stackoverflow.com/questions/1...ored-procedure

    so you can create a query, which you should ben be able to use as a recordsource

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I would second the comment on linking the tables. Perhaps using a connection string in VBA and a DSN-less connection would be easiest. You can install the correct SQL Server driver on each of the clients.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access file problem
    By Samar in forum Access
    Replies: 1
    Last Post: 11-09-2015, 10:04 AM
  2. Converting Access Data to XML according to a certain XSD file
    By mklindquist in forum Import/Export Data
    Replies: 2
    Last Post: 07-06-2015, 05:39 AM
  3. Replies: 7
    Last Post: 06-01-2015, 04:04 PM
  4. Replies: 10
    Last Post: 11-21-2014, 04:49 PM
  5. Replies: 3
    Last Post: 11-03-2013, 01:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums