|
|
Hello all, Maybe I am not understanding how calling a function is
supposed to work or just doing something silly. I am linking to SQL
tables in Access.
Here is my code and i will try and explain it step by step as I see it
Function LinkTables()
Dim db As Database, rs As Recordset, tblName As String
Set db = CurrentDB
***Table that has the names of all the tables I need to link to***
Set rs = db.OpenRecordset("SELECT tblnames FROM tblTableNames")
Do While Not rs.EOF
*** This one sets the varibles to the first table "Crew" works fine**
tblName = rs!tblnames
**Calling the next function for the actual link***
Call TableExists("tblName")
rs.MoveNext
Loop
rs.Close
End Function
**Here is where I am confused below is strtablename and that shows up as
tblname from above and i thought that should be Crew the value its
passing***
Function TableExists(strTableName As String) As Boolean
TableExists = Nz(DLookup("[Id]", "[MSysObjects]", "[Name]='" &
strTableName & "'"))
If TableExists = False Then
***This is the part where I thought it was supposed carry over the crew
table name for me after the actable part but what it does is put in
tblName as the variable not what i need***
DoCmd.TransferDatabase acLink, "ODBC",
"ODBC;DSN=MTEMIS-BDC;UID=xxxX;PWD=XXXXX;DATABASE=MarkTally", acTable,
"dbo." & strTableName, " '" & strTableName & "'", False
Else
DoCmd.DeleteObject acTable, "tblcrew"
DoCmd.TransferDatabase acLink, "ODBC",
"ODBC;DSN=MTEMIS-BDC;UID=xxxx;PWD=XXXXXX;DATABASE=MarkTally", acTable,
"dbo.TreeVolume", "tblTreeVolume", False
End If
End Function
I hope this was as clear as mud for everyone.. TIA
|
|
Remove the quotes around tblName in the call to TableExists:
Call TableExists(tblName)
With the quotes, it's assuming you have a table named "tblName"
Of course, TableExists is probably incorrect as well. If the table passed to
it exists, it's always going to do:
DoCmd.DeleteObject acTable, "tblcrew"
DoCmd.TransferDatabase acLink, "ODBC",
"ODBC;DSN=MTEMIS-BDC;UID=xxxx;PWD=XXXXXX;DATABASE=MarkTally", acTable,
"dbo.TreeVolume", "tblTreeVolume", False
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Brian" <nixtootec[ at ]hotmail.com> wrote in message
news:%23ir7Ve8LJHA.4376[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Hello all, Maybe I am not understanding how calling a function is supposed
> to work or just doing something silly. I am linking to SQL tables in
> Access.
>
> Here is my code and i will try and explain it step by step as I see it
>
>
> Function LinkTables()
> Dim db As Database, rs As Recordset, tblName As String
> Set db = CurrentDB
> ***Table that has the names of all the tables I need to link to***
> Set rs = db.OpenRecordset("SELECT tblnames FROM tblTableNames")
> Do While Not rs.EOF
> *** This one sets the varibles to the first table "Crew" works fine**
> tblName = rs!tblnames
> **Calling the next function for the actual link***
> Call TableExists("tblName")
> rs.MoveNext
> Loop
> rs.Close
> End Function
>
> **Here is where I am confused below is strtablename and that shows up as
> tblname from above and i thought that should be Crew the value its
> passing***
> Function TableExists(strTableName As String) As Boolean
> TableExists = Nz(DLookup("[Id]", "[MSysObjects]", "[Name]='" &
> strTableName & "'"))
> If TableExists = False Then
>
> ***This is the part where I thought it was supposed carry over the crew
> table name for me after the actable part but what it does is put in
> tblName as the variable not what i need***
>
> DoCmd.TransferDatabase acLink, "ODBC",
> "ODBC;DSN=MTEMIS-BDC;UID=xxxX;PWD=XXXXX;DATABASE=MarkTally", acTable,
> "dbo." & strTableName, " '" & strTableName & "'", False
> Else
> DoCmd.DeleteObject acTable, "tblcrew"
> DoCmd.TransferDatabase acLink, "ODBC",
> "ODBC;DSN=MTEMIS-BDC;UID=xxxx;PWD=XXXXXX;DATABASE=MarkTally", acTable,
> "dbo.TreeVolume", "tblTreeVolume", False
> End If
> End Function
>
>
> I hope this was as clear as mud for everyone.. TIA
|
|
that worked thanks for catching my oversite.. as long as the table
exists i can get at the information so works as i need it too..many thanx
Douglas J. Steele wrote:
[Quoted Text] > Remove the quotes around tblName in the call to TableExists:
>
> Call TableExists(tblName)
>
> With the quotes, it's assuming you have a table named "tblName"
>
> Of course, TableExists is probably incorrect as well. If the table passed to
> it exists, it's always going to do:
>
> DoCmd.DeleteObject acTable, "tblcrew"
> DoCmd.TransferDatabase acLink, "ODBC",
> "ODBC;DSN=MTEMIS-BDC;UID=xxxx;PWD=XXXXXX;DATABASE=MarkTally", acTable,
> "dbo.TreeVolume", "tblTreeVolume", False
>
>
|
|
"Brian" <nixtootec[ at ]hotmail.com> ????????????????
news:#ir7Ve8LJHA.4376[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Hello all, Maybe I am not understanding how calling a function is supposed
> to work or just doing something silly. I am linking to SQL tables in
> Access.
>
> Here is my code and i will try and explain it step by step as I see it
>
>
> Function LinkTables()
> Dim db As Database, rs As Recordset, tblName As String
> Set db = CurrentDB
> ***Table that has the names of all the tables I need to link to***
> Set rs = db.OpenRecordset("SELECT tblnames FROM tblTableNames")
> Do While Not rs.EOF
> *** This one sets the varibles to the first table "Crew" works fine**
> tblName = rs!tblnames
> **Calling the next function for the actual link***
> Call TableExists("tblName")
> rs.MoveNext
> Loop
> rs.Close
> End Function
>
> **Here is where I am confused below is strtablename and that shows up as
> tblname from above and i thought that should be Crew the value its
> passing***
> Function TableExists(strTableName As String) As Boolean
> TableExists = Nz(DLookup("[Id]", "[MSysObjects]", "[Name]='" &
> strTableName & "'"))
> If TableExists = False Then
>
> ***This is the part where I thought it was supposed carry over the crew
> table name for me after the actable part but what it does is put in
> tblName as the variable not what i need***
>
> DoCmd.TransferDatabase acLink, "ODBC",
> "ODBC;DSN=MTEMIS-BDC;UID=xxxX;PWD=XXXXX;DATABASE=MarkTally", acTable,
> "dbo." & strTableName, " '" & strTableName & "'", False
> Else
> DoCmd.DeleteObject acTable, "tblcrew"
> DoCmd.TransferDatabase acLink, "ODBC",
> "ODBC;DSN=MTEMIS-BDC;UID=xxxx;PWD=XXXXXX;DATABASE=MarkTally", acTable,
> "dbo.TreeVolume", "tblTreeVolume", False
> End If
> End Function
>
>
> I hope this was as clear as mud for everyone.. TIA
|
|
|