use code to link external mdb table


How to use code to link to a table in another Access mdb file?


Sponsored Links:



I splited program and database as two mdb file, app.mdb and data.mdb. All tables are stored in the data.mdb file.
How to use code to link all tables in the data.mdb whe app.mdb open every time?




I don't see this one already posted, so heeeere we go.

I want to be able to link external tables programmatically by using a macro to do the linking. The macro would be fed table names and I want it to make the links, looping back to repeat until it runs out of values/table names. My specific question is -- how do you call the linking process from within the macro? I know about the Run Command | Link Tables option, but it just presents a dialog box to browse to the table - same as if I were to do it manually. I don't want to see the dialog box - I want to to supply the table name as a variable, then have the macro do the link. Seems simple, but I don't see a way to do this. Can anyone tell me what I am missing?

Thanking you in advance,
Scott/Chicago, IL USA




I think this is a common issue for all those people who are upgrading from access to SQL server (as database), so i'm asking where i can find some resource to accomplish the following "basic" task.

The following command is used to attach an Access Table contained in an .mdb file:
DoCmd.TransferDatabase acLink, "Microsoft Access", "c:myappmydb.mdb", acTable, "target tablename", "local tablename"

How do you attach a table contained in an SQL server 2005 database?
Say that the database is on a server called MYSQLSERV, which contains a database called MYAPPDATABASE, that contains the table "MYTABLE"...

I tried the following, but i didn't succeed:
I created an "ODBC source" called MYODBC, linked to the correct server and to the correct database (I tested and I was able to link and browse the data in a table), then I tried this command:
DoCmd.TransferDatabase acLink, "ODBC Databases", "MYODBC", acTable, "target tablename", "local tablename"
I got the following error:
Runtime error 2507: The ODBC Databases type isn't an instyalled database type or doesn't support the operation you choose.
What am I missing?




Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.

Thanks,

Kurt




There are about 500 tables in SQL server.
How to code to link all SQL server tables via odbc?




Is it possible to link external file again when database is ready and the source file has been moved to other location?
And going farther -> is it possible to create some kind of dialog wich prompts user to point the external file as linked?

Thanks!




I searched and couldn't find anything on this specific issue.

I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.

The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.

Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.




I have used an Access 2003 MDB to link to two .db files in a Paradox Database while the PC is running with Windows XP.
The Paradox Database is located within a sub-folder in "Program Files". There has been no problem, so far.

I have now transferred both the Access MDB and the Paradox database to a new PC running with Vista Home Premium.
The Paradox Database is located within C:UsersPublicPublic Documents (by the Program which normally reads/writes to them, and still works) and the Access MDB is located in a sub-folder on a D: Drive.

I can open the MDB and attempt to re-link to the .db files, but an error is flagged up by the Linked Table Manager "Unexpected error from external database driver (11265)" with an OK button to close.

I have tried copying the Database and the Access MDB together to a folder on the D: drive, but the same error message appears.

I am unable to find any mention of this message in Microsoft Knowledgebase - can anybody advise, please?




Hi,

Basically I am trying to link a SQl Table through code without having to go to tools and then using add linked table manager and then refresh.The above steps I am trying to code.
I have this function in a module and use the command button of a form to call the function. Though I hard coded the DSN to the database belonging the SQL Sever, still prompts me for a user to select the DSN, how could I avoid it. I am breaking my heads. Please any advice.
The DSN already exists and is created through Settings then control panel and then Data Sources(ODBC).
I have no clues as to what is going on.

Thanks in advance

This is my code

Public Function Linktabledao()

Dim strlinkname As String
Dim strdbname As String
Dim strtablename As String
Dim strdsnname As String
'links or relinks a single table
'returns true or false based on err value

strlinkname = "satya"
strdsnname = "Freslib Production"
strtablename = "satya"
strdbname = "Freslib"

Dim db As Database
Dim tdf As TableDef

On Error Resume Next
Set db = CurrentDb
'if the link already exists, delete it
Set tdf = db.TableDefs(strlinkname)
If Err.Number = 0 Then
db.TableDefs.Delete strlinkname
db.TableDefs.Refresh
Else
'ignore error and reset
Err.Number = 0
End If
'create a new tablede object
Set tdf = db.CreateTableDef("satya")
tdf.Connect = "ODBC;Database = " & strdbname & ";DSN = " & strdsnname & ";Integrated Securtiy = True"
tdf.SourceTableName = strtablename

'append to the database's tabledefs collection

db.TableDefs.Append tdf
Linktabledao = (Err = 0)

End Function




Hi all

I am trying to write the code to delete an existing table, create a new one linked to a SQL Server db using OLE DB Provider connection string and Trused Connection

unfortunately I get an error 'could not find installable ISAM' when I try to append my new table top the tabledefs collectionin my Access DB (MS Access 2003).

Quote: Dim sConnStr As String
Dim oTable As TableDef
Dim sDestinationTable As String
Dim dbs As Database
Dim tbl As DAO.TableDef
Dim tblLinked As DAO.TableDef

sDestinationTable = "SQL Rulesnew"
Set dbs = CurrentDb
CurrentDb.CreateTableDef "SQLRules_" & VBA.Format(Now, "ddmmyyyy")

sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Hermes_Rep;Data Source=fos"

' below command also wouldn't work so commented!
'DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes_Rep;Trusted_Con nection=Yes", acTable, "[Report SQLRulesnew]", "SQLRules" & VBA.Format(Now, "ddmmyyyy")

' if table exists, delete it
If DCount("*", "MSysObjects", "[Name]='[SQL Rulesnew]' AND [Type] In (1, 4, 6)") > 0 Then
DoCmd.DeleteObject acTable, sDestinationTable
End If

Set tblLinked = dbs.CreateTableDef(sDestinationTable)

tblLinked.Connect = sConnStr
tblLinked.SourceTableName = sDestinationTable
dbs.TableDefs.Append tblLinked
tblLinked.RefreshLink notice that the source table name is [Report SQLRulesnew] so I believe I have to enclose it in square brackets is that right?

Also, is it possible to just iomport the data without a link?

I tried using Docmd.TransferTable with acImport but no luck with that either.

thanks for showing me where I am going wrong.
Philip




I am an extremely frustrated (And/Or slow) learner who has been trying to
learn how to use code in "M.S.Access" (On and off for about a year now)
but without much success. I have embarked on what should be a VERY simple
"Timesheet" project.

Its easy to add and edit records using the Switchboard Manager tools ETC,
but I have no idea how to validate (and then ADD or NOT add) data BEFORE
the database is updated.

My Database consists of 2 tables - called "Users" and
"timesheet" respectively.

The "Users" table consists of the following fields.
UserID Text - Primary Key
Name Text
Surname Text
LoggedIn Yes/No
LastLoggedIn Date (Medium)

The "TimeSheet" table contains the fields
Record AutoNumber (Primary Key)
UserId Text
Date Date (Medium)
TimeIn Time (Medium)
TimeOut Time (Medium)
Remarks Text

The very first form is used to "AddNewUsers" and should perform
the following checks.

1. A user cannot enter an exisiting "UserID" by virtue of the fact
that the "UserID" is the Primary key. (No duplicates allowed)

2. A "UserID" must consist of at least 3 characters and 1 number.
(Surprize - I know how to check this.)

3. Check that the user is not adding a duplicate record with
a different code. (I.E. Check that "Name" and "Surname"
does not exists already.)


Questions
A. How do I convert the text to uppercase before writting it
to the table? (This to ensure that the data is always
entered in the same case for validation purposes.)
I keep getting an error when using the "Upper" , "ConverToUpper"
orthe "UpperCase" function.
(Is "Function" the correctword to use here ??)

B. I tried to create the "AddNewUsers" form where the fields are
NOT bound to any records, but when trying to check if a user
already exists, I got an error saying that a field must be
bound to a record. I therefore included the "UserID" field
on the form.

Surely If I am adding a new record, then the fields should not
be bound ? There must be a way around this problem ?

c. The "AddNewUsers" form contains the following fields....
MyUserID - To get the "UserID"
MyName - Get the users name
MySurname - Get the users Surname
UserID - Listbox (So that the form is bound to
records in some way.)

....and (Taking tips from the so-called "Help") the following code.
(Please see comments in this code.)

Private Sub MySurname_LostFocus()
Dim dbsTimesheet As Database
Dim rstMyUsers As Recordset
Dim strUserName As String
Dim strUserName2 As String

' set the database and recordset variables
Set dbsTimesheet = OpenDatabase("timesheet.mdb")
Set rstMyUsers = dbsTimesheet.OpenRecordset( _
"SELECT UserId, Name, Surname from Users", dbOpenSnapshot)

'How do I get the system NOT to re-open the database? - I tried
'the following statement but then I get an error at the point
'at the "With rstMyUsers..... End With".......
'rstMyUsers = "SELECT UserId, Name, Surname from Users"

'Set the strUserNamevariables
strUserName = "Name = " & "'" & MyName & "'" & " and Surname = " & "'" & MySurname & "'"
strUserName2 = " " & MyName & " " & MySurname

With rstMyUsers
' Populate recordset.
.MoveLast
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strUserName

'If the user details are not found, then
'add a new record to the database.
If .NoMatch Then
rstMyUsers.AddNew
UserId = Text10
Name = MyName
Surname = MySurname
rstMyUsers.Update
Else
'If user already exists tell the use and .....
MsgBox "A record already exists for " & strUserName2
'...Reset the field names to blank
Text10 = ""
MyName = ""
MySurname = ""
End If
End With

End Sub


P.S. I am in possession of the following books, but have not been able to
find any help for my specific questions.

1. Mastering Access.
2. SAMS - Teach yourself Visual Basic in 24 Hours.
3. Examprep - Visual Basic desktop.

Is there a manual / tutorial that teaches how to create a "PROPER" application ?
I have found that any of the "northwind" type samples do not describe howe
to do validation as described above. I.E. Customers, Suppliers and even
Staff can be dulicated in these sample databases.

ANY help would be truly appreciated.




I am creating a system that logs visits to site, using a sql backend, access front end. Now loading the front end I have a procedure which creates a local mdb file in the users my documents folder. The purpose of the local mdb file is that it gives the user an offline record of all their visits.

When there are design changes which involve additional tables adding to the local mdb, I want to run on startup a sql query to check whether table exists in the local mdb. So I do a Select Query of MSysObjects where name='tblname'.

However when I load up the front end I get an error saying Record cannot be read, no read permission of MsysObject. Now I can obviously go into the VB Created Mdb and alter the permissions. However that is no good when I need to distribute to 100 workstations.

What I need is, VB code to alter the permissions on the MsysObject, when I create the Local Mdb.

Any Idea's??




Hi everyone,

I'm using 'SELECT Null As Column INTO TempTable FROM ExistingTable' type statements to populate a temporary table, rather than an ALTER TABLE statement.

When using 'Null As' the data type in the table is binary.

Using 'No As' results in a number type data.

What I am interested in is if anyone knows what the code would be to create String and Date data types, or if anyone knows any useful links/resources for this.

I've looked all over for anything to do with this, any help would be greatly appreciated.

Thanks

The Poot




I am charged with accessing all Excel applications on a server programmatically checking for code and hard links to SQL Server and Oracle with hard coded passwords, i.e. looking for connection strings to external databases with hard coded passwords.

I've done this before using Access to check table links but not the VBA code by attaching external mdb tables to my database and then checking the MSysObject table connections for each table. I didn't check the VBA code for coded connections.

I really need to check the Access and Excel VBA code too with from an external program.

Any suggestions or direction would be appreciated.

Thank you in advance for your courtesies.




I would like to delete tables using code where part of the table names have a certain value.

So the table have as their first 13 characters the following string

tbl_req_tmp_a..

I would like code to iterate through the tables collection and delete all tables that have this as the first 13 characters of their name..not sure of the exact syntax (e.g. is it tabledef or tabledefs), but something like:

dim tbl as tabledef(s)

for each tbl in tabledef(s)
where left(tblname,13) = 'tbl_req_tmp_a"
docmd.deleteobject actTable, tbl
next tbl

Thanks for any assistance..




I have a table (tblCovers), and in that table one of the columns (OpenCover) is a 'Yes/No' column/field.

Using code, how can I set all fields in that column to 'No', when clikcing a command button?

Best regards
Anders




I'm trying to use code to relink the the table but that is not the only table that is linked in the DB. The other linked tables are in other databases so I want to select specific tables to relink as the others may not need it. I alway will want to select the path.

here is an example of the code I'm using I got it from one of the other users here.




I'm trying to figure out how to link a text file to my database with VBA. I know how to do this for other types of data sources, using the DoCmd.TransferDatabase acLink command, but need guidance using this with TXT files.

Thanks,


Randy




I found 2 examples of code to refresh my attached table link to sharepoint lists.

I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.

My problem is my tables disconnect from sharepoint 2010 and the update fails.

I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.

Am I at least on the right track? I just want to know before I spend many hours getting this code to work.

These are the two locations of code I found.
http://www.access-programmers.co.uk/...d.php?t=211688
http://blogs.office.com/b/microsoft-...nk-tables.aspx

I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.

Thanks for any advice