Data stored in form

[Peter]

Hello,
I am in the process of upsizing an Access 2000 database to SQL Server 2005.

In the process, I have noticed that a form that is in use does not corespond
with a table in the access database. It appears that all of the data is
stored in the form itself.

1) Is this possible?
2) If it is, is there a way to transfer the structure to a table in this
access database
3) If 2 is possible, can I move the data records over to said table?

Thanks,
Peter

 

[Stefan Hoffmann]

hi Peter,

In the process, I have noticed that a form that is in use does not corespond
with a table in the access database. It appears that all of the data is
stored in the form itself.

1) Is this possible?

Yes, but I don't think this is the case.

My guess: this form is using a query to gather its information or it is
using VBA to do so.

What you should do:

1. Create a new SQL Server database.
2. Take a copy of your original mdb.
3. Use the Access Upsizing wizard and move only the tables to the SQL
Server and let it change your database.

In mostly all simple Access applications this is enough to get a working
Access front-ent with a SQL Server back.end.


mfG
--> stefan <--

 

[Peter]

Thanks for answering.

It really looks like this form is storing the data. When I right click the
top left corner of the form screen (from design mode) and select 'Datasheet
View', it is populated with all of the data that is in each of the 10,000
records that display at the bottom of the form.

It just seems like such an odd way of doing, and when I tried upsizing, the
table and data was never added to the newly created SQL database.

I had tried the upsizing wizard yesterday and this is how I began to
discover this.

 

[Linq Adams via AccessMonster.com]

I'm absolutely fascinated by the concept of storing data in an Access form!
Exactly how do you do that, Stefan?

 

[John W. Vinson]

Thanks for answering.

It really looks like this form is storing the data. When I right click the
top left corner of the form screen (from design mode) and select 'Datasheet
View', it is populated with all of the data that is in each of the 10,000
records that display at the bottom of the form.

It just seems like such an odd way of doing, and when I tried upsizing, the
table and data was never added to the newly created SQL database.

I had tried the upsizing wizard yesterday and this is how I began to
discover this.

I'm absolutely and unequivocally certain that the data is NOT stored in the
form. Access simply doesn't *do* that!

Open the form in design view. View its Properties. What is in the form's
"Recordsource" property?

 

[Rob Parker]

Hi Peter,

First, and most importantly: forms do NOT store data.

If your form is displaying thousands of records, they are coming from a
recordsource which the form is bound to. In design view, the name of the
table or query which is the form's RecordSource will be displayed in the
data tab of the property sheet. It is that table (or tables, if the
recordsource is a query based on multiple tables) that will need to be
transferred to the SQL Server database.

HTH,

Rob

 

[Peter]

Well, the form name is "Customer Information."

Under the form properties window, data tab, the record source is "Customer
Information"

I'm pretty confused by the situation.

Peter

 

[Linq Adams via AccessMonster.com]

Ah, come on John! I wanted to see how big a hole Stefan would dig for himself
trying to get out of his statement that yes, forms can store data!

;0)>

 

[Mike B]

Well, you could hard code an array (or an XML file as a string) then write
to disk and attach the diskfile either as a text datasource or as an XML
file, so even though you _could_ store data, you wouldn't be able to change
it except through the IDE.

So give him a "half p-ass". ;-)

 

[Bob Quintal]

Well, the form name is "Customer Information."

Under the form properties window, data tab, the record source is
"Customer Information"

I'm pretty confused by the situation.

Peter

Access is quite happy to allow a form, a report, and a view (either a
table or a query) have the same name.

If you cannot see either a table or query with this name, check the
Tools->Options->View->Hidden Objects box to see it.

Bob

 

[Stefan Hoffmann]

hi,

Ah, come on John! I wanted to see how big a hole Stefan would dig for himself
trying to get out of his statement that yes, forms can store data!

This is way to easy: Using a self-modifing code behind module... Not
nice to see, but possible to do so

mfG
--> stefan <--

 

[John W. Vinson]

Well, the form name is "Customer Information."

Under the form properties window, data tab, the record source is "Customer
Information"

In that case select Tools... Options... and check the "Hidden Objects" and
"System Objects" checkboxes. I'm not sure where these options are in 2007, but
they're somewhere behind the Office "medallion" button.

Then look in the Tables and Queries window and see if there is an object named
"Customer Information".

I'll gladly take your database and migrate it to SQL/Server free of charge if
there isn't *some* object (other than the form) of that name.

 

[Peter]

Duh!

/bangs head against wall

That was it! Thanks everyone!

 

[David W. Fenton]

First, and most importantly: forms do NOT store data.

While correct technically, it's possible for displayed data to
actually be in the form, e.g., in the RowSource of a combo/listbox.
That's not what's going on here, and I think it's almost always a
mistake for RowSources of combo/listboxes to be anything other than
SQL strings or callback functions, it is certainly the case that
there can be data stored there.

All that said, it's clearly not what's going on in this case.