Splitting Database?

[BillD]

Novice but important question-
Is there any reason to split an Access Database into a front end and back
end if I am converting to SQL Server 2005 Express with linked tables to a
Front End?
Am I correct in saying that if you are converting your tables and some
queries to SQL Server 2005 Express, that you should not split the Database
before converting as you would not be able to convert Queries?

 

[Stefan Hoffmann]

hi Bill,

Is there any reason to split an Access Database into a front end and back
end if I am converting to SQL Server 2005 Express with linked tables to a
Front End?

Nope, not really.

Am I correct in saying that if you are converting your tables and some
queries to SQL Server 2005 Express, that you should not split the Database
before converting as you would not be able to convert Queries?

This is not correct as it is regardless of which kind your tables are
(local or linked).



mfG
--> stefan <--

 

[BillD]

All the Tables are linked to the SQL Server Database.

 

[BillD]

Tables ar all linked to the SQL Server 2005 Express on a local network PC. In
the Access Front End The table properties read "Type-Linked ODBC"
Another problem is that I am not able to see where these tables are linked to.
I am not a programmer but a have a lot of experience with Access Databases.
Thanks again
Bill D

 

[Stefan Hoffmann]

hi Bill,

Tables ar all linked to the SQL Server 2005 Express on a local network PC. In
the Access Front End The table properties read "Type-Linked ODBC"
Another problem is that I am not able to see where these tables are linked to.
I am not a programmer but a have a lot of experience with Access Databases.

So you actually have already all tables on your SQL Express and like to
migrate your queries?

mfG
--> stefan <--

 

[BillD]

Yes, thanks to SQL Server Migration Assistant, I already have all tables on
SQL Server 2005 Express . I selected the queries option when I preformed the
Task but don't know if the queries were migrated. I would also like to know
if there is an easy way to tell where the tables are linked to. The path info.

 

[Stefan Hoffmann]

hi Bill,

Yes, thanks to SQL Server Migration Assistant, I already have all tables on
SQL Server 2005 Express . I selected the queries option when I preformed the
Task but don't know if the queries were migrated.

Have you installed SQL Server Management Studio (SSMS) Express?

http://www.microsoft.com/downloads/...FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796

I would also like to know if there is an easy way to tell where the tables are linked to. The path info.

Type in the immediate window

?CurrentDb.TableDefs.Item("yourLinkedTable").Connect

and

?CurrentDb.TableDefs.Item("yourLinkedTable").SourceTableName


mfG
--> stefan <--

 

[BillD]

Hi Stefan,
Yes, I have installed SQL Server Management Studio (SSMS) Express.

Type in the immediate window

What immediate window are you referring to in this statement?
Sorry-but I am not familiar with this.
Bill D

 

[Stefan Hoffmann]

hi Bill,

Yes, I have installed SQL Server Management Studio (SSMS) Express.

Here you can use the tree on the left to navigate to the views section
of your migrated database.

What immediate window are you referring to in this statement?
Sorry-but I am not familiar with this.

The immediate window is from the VBA IDE. Hit Ctrl+G in Access or in the
VBA IDE, it is normally opened at the bottom of the VBA window.


mfG
--> stefan <--

 

[BillD]

Stefan:
I opened the front End Access Database . The Tables in this Database are
Linked to the SQL Server Database. I opened the databse and selected the
Objects "Tables". I then pressed "Ctl+G". The immediate window opened at the
bottom of the screen.
The top of this screen shows in one box "General" and in the other box
"Declarations".
I typed in the "immediate window":
?CurrentDb.TableDefs.Item(VoterInformationTable).Connect
and
?CurrentDb.TableDefs.Item(VoterInformationTable).SourceTableName
and then hit "Enter"
I received the following message:
Item not found in this collection. (Error 3265)
An attempt to reference a name in a collection failed.
Possible causes:
The object does not exist in this collection. Make sure the object is
appended to a collection before referencing it.
There is more than one object with this name in the collection; using its
name is an ambiguous reference. Reference the object by its ordinal position
in the collection (for example, Recordsets(3)).

Any suggestions here.
Thanks again,
Bill D

 

[Stefan Hoffmann]

hi Bill,

?CurrentDb.TableDefs.Item(VoterInformationTable).Connect

You need to enclose the table name into double quotes:

?CurrentDb.TableDefs.Item("VoterInformationTable").Connect


mfG
--> stefan <--

 

[BillD]

I did not receive notification that you had responded to my last post, so I
checked out the thread and found your response this morning.
Thanks you ever so much for your valued advice.

Bill D

 

[BillD]

Stefan:
The procedure you gave me returns "dbo.VoterInformationTable" This tells me
the SQL table that the Front End Access Database is linked to. I would also
like to know the Databse that it is linked to. Would you be kind enough to
give me this procedure.
Thanking you in advance.
Bill D

 

[Stefan Hoffmann]

hi Bill,

The procedure you gave me returns "dbo.VoterInformationTable" This tells me
the SQL table that the Front End Access Database is linked to.

This is the value returned by .SourceTableName.

I would also
like to know the Databse that it is linked to. Would you be kind enough to
give me this procedure.

This is maybe part of the value returned by .Connect. It is either
Catalog=Name or Database=Name.

In some cases the database may not be listed here, but this due to the
concept of the connectiong string.


mfG
--> stefan <--

 

[BillD]

Stefan:
I have it. In the Immediate Window, I was typing in both statements and then
hitting return at the end of the second statement.When I hit return after the
first statement "....Connect", I get the following:

"ODBC;DRIVER=SQL
Server;SERVER=BILL\SQLEXPRESS;APP=SSMA;WSID=BILL;DATABASE=db1;Network=DBMSLPCN;Trusted_Connection=Yes"

Then I hit return after the second statement and get ;
"dbo.VoterInformationTable"
That's it.
Thanks ever so much.
Wish there was an easy way to get a report on all tables.

Bill D