ADP Collect Data Feature

[sullyman]

Hi folks - do you know if it is possible to use the Collect Data Feature (e.g. create email, manage replies) to send forms to users to automatically import into the DB by using an Access 2007 adp version file and SQL Server 2005? Currently in my 2007 adp version, the feature is not available (not enabled)

 

[boblarson]

It would not be available because that feature is only available with the new ACCDB format and not with older adp, mdb formats.

 

[sullyman]

Pity. I have been messing about with it for last hour or so and it's a great feature. I am currently developing a new application using Adp connecting to SQL. From reading online, there seems to be a lot of differences on using adp, mdb or accdb to achieve editing data in sql server 2005. Any experiences Bob with same?

 

[boblarson]

I've always used either mdb or accdb when using a SQL Server as a backend. I've not used an ADP and the prevailing suggestion from Microsoft is to go with an mde/mdb/accde/accdb frontend instead of using an adp.

 

[sullyman]

Thanks Bob. What would be involved in converting my current adp file back to an accdb file. I have only one form as it's a test project.

 

[boblarson]

Thanks Bob. What would be involved in converting my current adp file back to an accdb file. I have only one form as it's a test project.

Start a new, blank 2007 database and then link the table(s) to SQL Server and then import the form from the other database.

 

[sullyman]

Thanks Bob - will try same and see how it goes. Would that mean that the Collect Data feature would also work in accdb format and update the data to sql server too

 

[boblarson]

Thanks Bob - will try same and see how it goes. Would that mean that the Collect Data feature would also work in accdb format and update the data to sql server too

It should, yes.

 

[sullyman]

? How do i link the tables to SQL in new accdb format. Do i need to run vb to create a connection etc.

 

[boblarson]

? How do i link the tables to SQL in new accdb format. Do i need to run vb to create a connection etc.

You can start off with using an ODBC connection. Go to the IMPORT Tab and then select OTHER and then select the SQL Server and then the dialog will allow you to select Import or LINK. Select LINK and then select the type to be ODBC and then select the ODBC connection you've made.

Once you have this all ready to go, you can then use the DSN-Less connection stuff here by MVP Doug Steele to let others use the frontend without having to have an ODBC connection defined.

 

[sullyman]

Thanks Bob. I think it was creating a DSN on all users machines was turning me off using an accdb version. I have test queries saved through SSMS. Can i still use these through link version or do i need to create them again on client side. Sorry for the questions, i am just trying to get my head around advantages of adp versus linked

 

[boblarson]

Thanks Bob. I think it was creating a DSN on all users machines was turning me off using an accdb version. I have test queries saved through SSMS. Can i still use these through link version or do i need to create them again on client side. Sorry for the questions, i am just trying to get my head around advantages of adp versus linked

If the queries are saved as views on SQL Server you can link to them just as if you linked a table. If they are not in SQL Server but in the ADP, which I don't think would be the case, then you would not be able to.

 

[sullyman]

Hi Bob, thanks, yes they are stored as views. One final question and i'm finished for the evening as my mind is in overload.

At present i am building combos from select statements. Is it possible to create a view of what should be in the combo and then from the combo use something like select "Viewname"

 

[boblarson]

Hi Bob, thanks, yes they are stored as views. One final question and i'm finished for the evening as my mind is in overload.

At present i am building combos from select statements. Is it possible to create a view of what should be in the combo and then from the combo use something like select "Viewname"

As long as you have the view created AND the view LINKED, you can use that in the combo box row source by saying

Select * FROM dbo_ViewName

or use individual fields

Select FieldName1, FieldName2 FROM dbo_ViewName

(normally the table and view objects come into the database linked with the dbo_ appended on to the name. I usually just leave those there because it makes life much easier if you have to delete the link and relink, especially with many table/views involved. However it is possible to just right click on them and select RENAME and take the dbo_ part off. But that can be a pain later if you ever have to delete to relink.)

 

[sullyman]

Thanks Bob, you've been a great help

 

[boblarson]

Thanks Bob, you've been a great help

Sure thing.