Connecting tables other that ODBC method (1 Viewer)

[Ashfaque]

Hi,

I would like to know if there are some code lines that could help in establishing connection between server tables into Access db whiile opening.
And disconnect after closing.

I know the ODBC linking method but it increase the MDE size and stays onnected even the db is swithced off.

I am using Access as FE and SQL Server Desktip version as BE (no password set up to log in server or its dbs. Just windows log in used).

With kind regards,
Ashfaque

 

[pdx_man]

I know the ODBC linking method but it increase the MDE size and stays onnected even the db is swithced off.

Huh, this is news to me. Do you have any documentation to support this? Have you tried doing a compact & repair after linking and before making your MDE file? Have you tried doing a compact & repair AFTER making your MDE file?

I am a DBA at my company and I monitor the connection very closely. Linked table connections via ODBC or any other method do not stay connected if the DB is not open. Even if the DB is open, a connection is only created when the table or another object referencing the table is instanciated. Even using ADO, the connection will be dropped when the DB is closed.

 

[Ashfaque]

pdx,

I tried MDB compression already then created MDE. I even did not use any picture into the forms. But the tables connected into the database holds huge records. Say around 400,000 records in each table and almost I have 27 tables.

I believe tables that linked thru ODBC has increased size of my db. Even the db is closed the size is same. I tried removing link and just checked the size. It reduced to 1/5 size of previous.

Thanks

 

[pdx_man]

First off, let's establish what the size is. If we are talking about a 5 MB database with linked tables and a 1 MB without, then let's not sweat this. There is going to be some overhead with the connection information.

The size of the linked tables is not going to matter. I have tables with millions of rows with dozens of columns. So, let's experiment.

Access 2003
Create New Database - 96K
Add one 'average' linked table - grows to 128K
Add one 'large' linked table - grows to 2,156K
Perform Compact and Repair - shrinks to 256K
Add one 'ignormously large' linked table (SQL reports this table as > 2GB) - Size remains at 256K
Remove one 'average' linked table - Size remains at 256K

So, as we can see, linked tables are not going to have a large impact on the overall size of your DB. This must be a symptom of another issue.

Search this forum for the decompile switch. I posted how to use this a while ago and I am sure there are some other postings. This will clear out the cobwebs that Access sometimes keeps around.

 

[Ashfaque]

Thanks PDX again

I broken DSN connectivity and remade it. Also I recompiled and compressed and 2-3 time ...found no error.

Better than before now...

Thanks pdx, your words make me feel that my db got not that much increased. Still there is lot of room when used ODBC.

Thanks

 

[pdx_man]

Just to be clear, use the /decompile switch when opening your database. Here are Pat's instructions:
http://www.access-programmers.co.uk/forums/showthread.php?t=113692&highlight=/decompile

Afterwards, recompile.

 

[Ashfaque]

Pdx,

Again thanks for the link.

With kind regards,
Ashfaque