Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
0
kwarden13
Asked:
kwarden13
  • 5
  • 5
1 Solution
 
BitsqueezerCommented:
Hi,

 - 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
0
 
burrcmCommented:
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
0
 
kwarden13Author Commented:
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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
BitsqueezerCommented:
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
0
 
kwarden13Author Commented:
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.
0
 
BitsqueezerCommented:
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
0
 
kwarden13Author Commented:
So I made a query with the fields I want and used that has my record set. Now the form doesn't load
0
 
BitsqueezerCommented:
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:
Application.SaveAsText acForm,"YourFormName","C:\YourFormName.txt"

Open in new window

which will save the form as text file to C:\ (or wherever you want) and upload it here.)

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
0
 
kwarden13Author Commented:
not sure I know what you are talking about. Can I upload my db and take out the sensitive info.?
0
 
BitsqueezerCommented:
Hi,

sure, you can do that, but as the backend is a SQL Server database the additional CREATE command is necessary to check the table structure you're using.

Hmm..at least it seems to be one..you only said you "converted it to SQL and linked it back with ODBC" so I guessed that it is SQL Server we are talking about. If it is not, could you please explain what you have on the other side of your ODBC connection?

Cheers,

Christian
0
 
kwarden13Author Commented:
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.

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now