odbc links

[paulevans]

sorry if thi9s is the wrong place but I thought I would try somewhere.

I would like to create links to table trough an odbc link.

whta I have is an estimation programme that creates 4 tables in a directory/folder each time you create a new estimate I have an odbc link that points to the given folder where the tables are being created. What I would like to do is to search this folder and link specific tables to my database which is a contract control programme. the tabels are given unique name for each estimate example names are: 0001_assembly, 0001_data, 0001_increment, 0001_system

so to sum up is it possible to create a lnk to these tables on the fly. any one got any ideas and possible codes samples for doing this.

Thanks

 

[namliam]

Why use seperate files? Why not use one Backend database 0001.mdb and link to that using 'normal' linked tables.

 

[KenHigg]

Here's a cut & paste from a pc of my code. I read through it and it should be obvious whats going on:

    Dim strTableName As String          'Table name to link to
    Dim strDBLocation As String         'Location of BE file
    
    Dim dbsCurrent As Database          'Connection var
    Dim tbfCurrent As TableDef          'Table def var
                    
    '--Set table name
    strTableName = "tblMain"
        
    '--Connection var
    Set dbsCurrent = CurrentDb()
    
    '--Set BE file name and location
    strDBLocation = Me!Text1
       
    '--Connection var
    Set dbsCurrent = CurrentDb()
       
    'Business end of the routine begins here
    '===========================================
    '--Create a new table definition in the current
    Set tbfCurrent = dbsCurrent.CreateTableDef(strTableName)
    
    'Set the table def's connect string
    tbfCurrent.Connect = ";database=" & strDBLocation
    
    'Set the table def's table name
    tbfCurrent.SourceTableName = strTableName
    
    '-- Add this TableDef to the current database.
    dbsCurrent.TableDefs.Append tbfCurrent

    MsgBox "Done!"

 

[paulevans]

Hi Thanks for quick response.

I can not keep these records in one table as the programme was not written by me and I have no control over what happens.

These tables are created by a piece of third party software.

 

[namliam]

Hi Thanks for quick response.

I can not keep these records in one table as the programme was not written by me and I have no control over what happens.

These tables are created by a piece of third party software.

Then try working out Ken's sample

 

[paulevans]

Hi Ken

coppied and used that however. The tables from my external programme are not mdb files and cause a problem when I load them. If i use file>get external data and link tables then I can access the informatiion. using the code you gave me I was able to find the file with a fileio routine I had that gives me the path to the file. but then gives an error on unrecognized database format.

 

[KenHigg]

Manually connect to the tables then look in the MSysObjects table and you should see the connection string. This will show you how the connection string you need to build should be structured...

 

[paulevans]

Hi the response from msysobjects is:
Connect
DSN=winone;Driver=C:\dbisam\odbc\stdsrc\ver3\lib\dbodbc\dbodbc.dll;
ConnectionType=Local;RemoteType=LAN;RemoteHostName=;
RemoteIPAddress=129.0.0.1;RemotePort=13005;RemoteService=;
RemoteReadAhead=50;MRUDatabase1=Memory;
MRUDatabase2=H:\winit\Estimates;
CatalogName=H:\winit\Estimates;ReadOnly=False;LockRetryCount=15;
LockWaitTime=100;ForceBufferFlush=False;
StrictChangeDetection=False;
MRUPrivateDirectory1=C:\DOCUME~1\pevans\LOCALS~1\Temp\;
PrivateDirectory=C:\DOCUME~1\pevans\LOCALS~1\Temp\

sorry very new to this what does this mean?

 

[KenHigg]

This is real close to my limit on this topic... I could figure the rest of it out if I had time, etc. Basically this is the connection string (or part of it) that you need to supply to connect to the data source. I've manipulated it to connect to access tables and sqlserver tables but I'm not sure what you're working with - Sorry. Maybe someone else will jump in. What kind of tables are you trying to connect to?

 

[paulevans]

Hi Ken

Thanks for all your input I will look at this a little longer.

If anyone else knows more please reply thanks

 

[jasn_78]

paul i am working on something similiar to you atm by the sounds of it i found the below link extremley helpful as a starting point. As im still a newbie at this not sure exactly on ur situation but hope this helps

http://support.microsoft.com/kb/210295