Solved

Access tables converted to SQL table (linked to on ODBC connection)

Posted on 2011-03-16
11
358 Views
Last Modified: 2012-08-13
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
Comment
Question by:kwarden13
  • 5
  • 5
11 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35154274
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
 
LVL 28

Expert Comment

by:burrcm
ID: 35154878
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
 

Author Comment

by:kwarden13
ID: 35158453
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
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35158610
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
 

Author Comment

by:kwarden13
ID: 35158742
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35159196
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
 

Author Comment

by:kwarden13
ID: 35160423
So I made a query with the fields I want and used that has my record set. Now the form doesn't load
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35161133
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
 

Author Comment

by:kwarden13
ID: 35168027
not sure I know what you are talking about. Can I upload my db and take out the sensitive info.?
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 35169132
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
 

Author Comment

by:kwarden13
ID: 35183389
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now