Ok, what you have to do is import the forms.
In fact, before you import the forms, you should create that blank database, and link up all of the tables.
(Use the linked table manager).
Make sure your linked tables work.
And make sure your linked tables have the SAME name
In access “odbc” that you are migrating to, if you link to a table called tblHotels, access will “usually” link the table as:
dbo_tblHotels.
So after linking the tables, you want to re-name above to tblHotels. This will be a linked table to sql server.
At this point, your form does NOT have some “connection”. And in fact ADO forms did not have a connection property.
So I not sure what you mean by your forms use that “connection”. They should not.
However, it is possible that you mean some VBA code in that form?
You can continue to use CurrentProject.Connection.
You “old” code likely looked like this:
Private Sub Command61_Click()
Dim rst As New ADODB.Recordset
rst.Open "select * from tblHotels", CurrentProject.Connection
rst.MoveLast
MsgBox rst.RecordCount
End Sub
The above is an “air code” samples.
To make this code work in your new form, y ou have to search + replace the “open” command. You code will become:
Dim rst As New ADODB.Recordset
rst.Open "select * from tblHotels", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
rst.MoveLast
MsgBox rst.RecordCount
Note how I was able to use the same code, and NOT have to change the use of the built in connection object.
The above code should work.
Depending on the ADO code, you may well not have to add “adOpenKeyset”, and the adLockBatchOptimistic, but I believe that “most” ADO project code did NOT include these extra keywords, but standard VBA + ADO reocrdsets will have to.
Also make sure you put the ado reference above the built in “DAO”, since all of your reocrdset code VERY likely was written using ADO, and if you don’t do this, lots of your code will break.
So you can continue to use CurrentProject.Connection in your code.
However, it is some what “confusing” you stating that the forms use this connection? The forms do not.
The VBA code will of course use the connection object.
So if the forms “data source” was a simple table name, or query? You likely can continue to use that table name.
For a query, you may well be able to use the existing “view” that the ADP project created.
So the form does not have some “hidden” connection.
If you have a ADP form without code?
Then just ensure that a linked table with the SAME table name exists. Then import the ADP form. At this point, ZERO changes should be required to that form in most cases.
The extra work will be checking the forms VBA code – and especially “ado” reocrdset code.
With linked tables, then no connection string(s) are required in your code – you can continue to use the built-in connection object access provides.
If you get these steps working in the right order, you will save HUGE amounts of effort and time.
Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
