kwarden13
asked on
Access tables converted to SQL table (linked to on ODBC connection)
I converted an access table to a SQL table. I then linked through the ODBC connection.
However, now my forms will not populate. The forms are a "adding records' form only. The only data in the table is data which is added to the table through these forms.
Please help as to why the form is not populating the fields.
However, now my forms will not populate. The forms are a "adding records' form only. The only data in the table is data which is added to the table through these forms.
Please help as to why the form is not populating the fields.
Also your original table names will be prefixed with dbo_. If you are using the original Access db as the front you will need to rename the tables back to the original names for Access to use them.
Chris B
Chris B
ASKER
Actually I think I just figured it out. Each form needs to have its own table. Right now it is built on the querybuilder in the data tab (properties on the form)
When i put a table as the data source it works
When i put a table as the data source it works
Hi,
I personally cannot recommend to set a table as record source. It is of course possible but that means that you create a "SELECT * FROM table" in the background. Normally every form should be based on a query which exactly defines the really needed columns, not more. Using the table name as record source has also the disadvantage that you can't set a custom order (without using the Order By settings but that slows down the thing more).
You should create a view on the server which fits your need and link it as a table to Access (Access handles views like tables) so the server filters and orders anything before any data is sent to the client.
But of course you can also use Access queries to do the same and they can be used as record source for any form, too. You only need to enclose the primary key column in the query.
Cheers,
Christian
I personally cannot recommend to set a table as record source. It is of course possible but that means that you create a "SELECT * FROM table" in the background. Normally every form should be based on a query which exactly defines the really needed columns, not more. Using the table name as record source has also the disadvantage that you can't set a custom order (without using the Order By settings but that slows down the thing more).
You should create a view on the server which fits your need and link it as a table to Access (Access handles views like tables) so the server filters and orders anything before any data is sent to the client.
But of course you can also use Access queries to do the same and they can be used as record source for any form, too. You only need to enclose the primary key column in the query.
Cheers,
Christian
ASKER
so should I just create a query? Right now the tables are built on the querybuilder not defined query in the database.
The problem with using the view is that you cannot update the table then.
The problem with using the view is that you cannot update the table then.
Hi,
that's what I meant: If you enclose the Primary Key in the view the form should be updatable (of course it must be an updatable snapshot and the form settings must allow updates).
First choice in using SQL Server should be a view on the server, second a saved query in Access (for example if you want to mix data from different sources).
Cheers,
Christian
that's what I meant: If you enclose the Primary Key in the view the form should be updatable (of course it must be an updatable snapshot and the form settings must allow updates).
First choice in using SQL Server should be a view on the server, second a saved query in Access (for example if you want to mix data from different sources).
Cheers,
Christian
ASKER
So I made a query with the fields I want and used that has my record set. Now the form doesn't load
Hi,
Can you post the CREATE command to create the table on the SQL Server? (With SQL Server Management Studio, click right mouse button on the table name in the object list, there you find the menus to script the command into a new window.)
Can you post the SQL command you used in Access to query the table in your form? (In the query editor, choose SQL view and copy and paste it here).
If you've made an SQL view and linked it as table like recommended, please click right mouse on the view name in SQL Server Management Studio and choose "edit" to script it into a new window and then copy and paste it here.
Can you post the form definition here? (In VBA editor in the immediate window, type:
If you've designed a query with the query editor and saved it in Access, try to open it without the form and see if it is updatable. If it is, then you have an error in the settings of your form. If it is not you made a mistake in the query definition.
If you have made a view on SQL Server and linked it as table in Access you should be able to open the view directly in Access like a normal table and it should be updatable. If it is not there is a problem in the view definition (like missing the Primary Key). You never told about the PK, if you have one and how it looks like.
Cheers,
Christian
Can you post the CREATE command to create the table on the SQL Server? (With SQL Server Management Studio, click right mouse button on the table name in the object list, there you find the menus to script the command into a new window.)
Can you post the SQL command you used in Access to query the table in your form? (In the query editor, choose SQL view and copy and paste it here).
If you've made an SQL view and linked it as table like recommended, please click right mouse on the view name in SQL Server Management Studio and choose "edit" to script it into a new window and then copy and paste it here.
Can you post the form definition here? (In VBA editor in the immediate window, type:
Application.SaveAsText acForm,"YourFormName","C:\YourFormName.txt"If you've designed a query with the query editor and saved it in Access, try to open it without the form and see if it is updatable. If it is, then you have an error in the settings of your form. If it is not you made a mistake in the query definition.
If you have made a view on SQL Server and linked it as table in Access you should be able to open the view directly in Access like a normal table and it should be updatable. If it is not there is a problem in the view definition (like missing the Primary Key). You never told about the PK, if you have one and how it looks like.
Cheers,
Christian
ASKER
not sure I know what you are talking about. Can I upload my db and take out the sensitive info.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I appreciate your help, however I was able to fix the problem. It seemed I had to re-create the forms using the new table (even though nothing changed except it was on the SQL Server) After removing fields and adding them back, it seemed to work.
Access is so odd sometimes.
Access is so odd sometimes.
- SQL Server needs at least a Primary Key on the table so Access can identify each record to save the data
- after converting the table and relink it to Access, did you change the record source of the form (maybe the name of the linked table is not the same and you are working with a local copy?)
- have you opened the query on which your form should be based on and tried to insert/edit records without the form?
- have you opened the table with SQL Server Management Studio directly on the server to check if the table records can be updated or if any of the newly added records are in the table?
- if you have changed the settings of the form to ONLY add records then it will show an empty form always on open and ONLY allow to add records, not to edit/view old ones. This is the "DataEntry" setting in the "Data" tab.
Cheers,
Christian