Excel for Life: 24 new messages in 10 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

* Insert large base64 into memo field - 2 messages, 2 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b33175be7d352d9?hl=en
* arrays and loop question - 3 messages, 2 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/510ff1f5cc111f4c?hl=en
* ISAM error when trying to create tablelink - 9 messages, 3 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3f2f7276868cdcb5?hl=en
* Send Object Action - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e749b518a225b04a?hl=en
* Is there an utility.wlib_AutoDial replacement? - 3 messages, 2 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/988f324f3ac928d8?hl=en
* Audit Log - 2 messages, 2 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0c40ff55056ae894?hl=en
* Correlation coefficent (CORREL in excel) - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/54b18330ca60e8ed?hl=en
* excel cell value - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4c9e7db119fdea74?hl=en
* Button in form header - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f72483eb1388258f?hl=en
* method 'range' of object'_global' failed on XL sort - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3da41c6385ca6abc?hl=en

==============================================================================
TOPIC: Insert large base64 into memo field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b33175be7d352d9?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 12:21 pm
From: "Pieter Wijnen"

Not as long as you always prefix ambigous Objects (Recordset for one)

Fastest way to add a reference is pressing Ctrl+G (Immediate Window)
Select Tools/References from the Menu
Select 'Microsoft DAO 3.6 Object Library'

Pieter

"Mike Iacovou" <MikeIacovou@discussions.microsoft.com> wrote in message
news:2E249FA9-9B5B-4F5B-B3BB-23AEA9A80E1F@microsoft.com...
> thanks gary.
>
> i'm a noob with access... comfortable with referencing in excel vba...
> will
> get there in the end...
>
> I get an 'unknown type' with the DIM DAO.Recordset - I obviously need to
> set
> some references somewhere... This is a vanilla standard Access install...
> in
> the past have have had inter-system problems based on references - will
> this
> be an issue if I add references in this project (and what am i missing ?)
>
> Appreciate the help... great ;)
>
> Mike

== 2 of 2 ==
Date: Wed, Nov 14 2007 12:21 pm
From: "Douglas J. Steele"

Sounds as though you're using either Access 2000 or 2002, neither of which
included the reference to DAO by default.

Go into the VB Editor and select Tools | References from the menu bar.
Scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it, then close the dialog.

DAO is a fundamental library to Access, so you shouldn't run into issues on
other machines.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

==============================================================================
TOPIC: arrays and loop question
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/510ff1f5cc111f4c?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 14 2007 12:23 pm
From: "Krzysztof via AccessMonster.com"

Good Afternoon,

I have a question i cannot seem to answer.

I have 2 arrays and a loop that perform certain functions, and when i call
this sub, i get and error message stating: "Compile Error: byRef Argument
Type Mismatch".
I am not sure what that means, but i have tried replacing the array
identifier with a variable, but whatever i do, i get the same message.

Here is a copy of the code:

Here is my function
Public Function TableExists(strTableName As String) As Boolean
On Error Resume Next

TableExists = IsObject(CurrentDb.TableDefs(strTableName))

End Function

And here is the sub
Public Sub CaptureData()

Dim Tables(3, 0), Queries(3, 0) As String
Tables(0, 0) = "tbl_INVOICE"
Tables(1, 0) = "tbl_WOITEMS"
Tables(2, 0) = "tbl_WRKORDER"
Tables(3, 0) = "tbl_EMPLOYEE"
Queries(0, 0) = "mtqry_Invoice"
Queries(1, 0) = "mtqry_WOItems"
Queries(2, 0) = "mtqry_Wrkorder"
Queries(3, 0) = "mtqry_Employee"

'===============================================
'Create local tables
'Check to see if local table exists and if so, delete it
Me.bonus_progress.Visible = True 'activate the progress bar
Me.bonus_progress.value = 0 'reset the progress bar

Dim i As Integer
For i = 0 To 3
If TableExists(Tables(i,0)) = True Then 'check to see if table
exists
DoCmd.DeleteObject acTable, Tables(i,0) 'if so, deletes it
Else
'do nothing
End If
DoCmd.OpenQuery Queries(i,0), acViewNormal 'rerun the query to
create new, updated table
Me.bonus_progress.value = Me.bonus_progress.value + 25
'increment the progress bar
Loop
'===============================================

Me.bonus_progress.Visible = False 'deactivate the progress bar

End Sub

Right after i set up the loop "Tables(i,0)" is what is highlighted.

Any ideas?

--
Message posted via http://www.accessmonster.com

== 2 of 3 ==
Date: Wed, Nov 14 2007 12:28 pm
From: "Krzysztof via AccessMonster.com"

Krzysztof wrote:
>Good Afternoon,
>
>I have a question i cannot seem to answer.
>
>I have 2 arrays and a loop that perform certain functions, and when i call
>this sub, i get and error message stating: "Compile Error: byRef Argument
>Type Mismatch".
>I am not sure what that means, but i have tried replacing the array
>identifier with a variable, but whatever i do, i get the same message.
>
>Here is a copy of the code:
>
>Here is my function
>Public Function TableExists(strTableName As String) As Boolean
>On Error Resume Next
>
> TableExists = IsObject(CurrentDb.TableDefs(strTableName))
>
>End Function
>
>And here is the sub
>Public Sub CaptureData()
>
> Dim Tables(3, 0), Queries(3, 0) As String
> Tables(0, 0) = "tbl_INVOICE"
> Tables(1, 0) = "tbl_WOITEMS"
> Tables(2, 0) = "tbl_WRKORDER"
> Tables(3, 0) = "tbl_EMPLOYEE"
> Queries(0, 0) = "mtqry_Invoice"
> Queries(1, 0) = "mtqry_WOItems"
> Queries(2, 0) = "mtqry_Wrkorder"
> Queries(3, 0) = "mtqry_Employee"
>
> '===============================================
> 'Create local tables
> 'Check to see if local table exists and if so, delete it
> Me.bonus_progress.Visible = True 'activate the progress bar
> Me.bonus_progress.value = 0 'reset the progress bar
>
> Dim i As Integer
> For i = 0 To 3
> If TableExists(Tables(i,0)) = True Then 'check to see if table
>exists
> DoCmd.DeleteObject acTable, Tables(i,0) 'if so, deletes it
> Else
> 'do nothing
> End If
> DoCmd.OpenQuery Queries(i,0), acViewNormal 'rerun the query to
>create new, updated table
> Me.bonus_progress.value = Me.bonus_progress.value + 25
>'increment the progress bar
> Loop
> '===============================================
>
> Me.bonus_progress.Visible = False 'deactivate the progress bar
>
>End Sub
>
>Right after i set up the loop "Tables(i,0)" is what is highlighted.
>
>Any ideas?

Ok, so i messed up on one thing, i replaced "Loop" with "Next i".
i still get the error though :(

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

== 3 of 3 ==
Date: Wed, Nov 14 2007 12:35 pm
From: "Pieter Wijnen"

Dim both your variables as string (Tables is dimmed as a Variant)
ie

Dim Tables(3, 0) As String, Queries(3, 0) As String

Personally I'd Use (matter of taste <g>):

Public Function TableExists(ByVal TableName As String) As Boolean
On Local Error Resume Next

Dim TDef As DAO.TableDef
Set TDef = Access.CurrentDb.TableDefs(TableName)
TableExists = Not (TDef Is Nothing)
Set TDef = Nothing
End Function

Also:
CurrentDb.Execute Queries(i,0), DAO.DbSeeChanges ' instead Of
DoCmd.OpenQuery

Pieter

"Krzysztof via AccessMonster.com" <u33796@uwe> wrote in message
news:7b3767798f0dc@uwe...
> Good Afternoon,
>
> I have a question i cannot seem to answer.
>
> I have 2 arrays and a loop that perform certain functions, and when i call
> this sub, i get and error message stating: "Compile Error: byRef Argument
> Type Mismatch".
> I am not sure what that means, but i have tried replacing the array
> identifier with a variable, but whatever i do, i get the same message.
>
> Here is a copy of the code:
>
> Here is my function
> Public Function TableExists(strTableName As String) As Boolean
> On Error Resume Next
>
> TableExists = IsObject(CurrentDb.TableDefs(strTableName))
>
> End Function
>
> And here is the sub
> Public Sub CaptureData()
>
> Dim Tables(3, 0), Queries(3, 0) As String
> Tables(0, 0) = "tbl_INVOICE"
> Tables(1, 0) = "tbl_WOITEMS"
> Tables(2, 0) = "tbl_WRKORDER"
> Tables(3, 0) = "tbl_EMPLOYEE"
> Queries(0, 0) = "mtqry_Invoice"
> Queries(1, 0) = "mtqry_WOItems"
> Queries(2, 0) = "mtqry_Wrkorder"
> Queries(3, 0) = "mtqry_Employee"
>
> '===============================================
> 'Create local tables
> 'Check to see if local table exists and if so, delete it
> Me.bonus_progress.Visible = True 'activate the progress bar
> Me.bonus_progress.value = 0 'reset the progress bar
>
> Dim i As Integer
> For i = 0 To 3
> If TableExists(Tables(i,0)) = True Then 'check to see if
> table
> exists
> DoCmd.DeleteObject acTable, Tables(i,0) 'if so, deletes it
> Else
> 'do nothing
> End If
> DoCmd.OpenQuery Queries(i,0), acViewNormal 'rerun the query to
> create new, updated table
> Me.bonus_progress.value = Me.bonus_progress.value + 25
> 'increment the progress bar
> Loop
> '===============================================
>
> Me.bonus_progress.Visible = False 'deactivate the progress bar
>
> End Sub
>
> Right after i set up the loop "Tables(i,0)" is what is highlighted.
>
> Any ideas?
>
> --
> Message posted via http://www.accessmonster.com
>

==============================================================================
TOPIC: ISAM error when trying to create tablelink
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3f2f7276868cdcb5?hl=en
==============================================================================

== 1 of 9 ==
Date: Wed, Nov 14 2007 12:25 pm
From: "Douglas J. Steele"

Check that the SourceTableName property is correct. If you're changing from
linking to Jet vs. linking to SQL Server, your SourceTableName will be
missing the owner (dbo.TableName, as opposed to TableName)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
>I am having a similar problem linking tables to SQLServer or a different
> access database. In my case, I get the ISAM error when I try to change the
> tabledef.connect from one to the other.
>
> The connection strings I use work if I am not changing from one type to
> another.
>
> ACCESS:
> ;Database=O:\FullPath\databasename.mdb
>
> SQL
> Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> Info=True
>
> So I can change to a different access database or I can change to a
> different SQL server or database in the same server.
>
> I've looked at Doug's code on
> http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried other
> methods.
>
> Id doesn't matter if the table to be changed is deleted first or not. If
> the
> tabledef is deleted the ISAM error occurs as the new tabledef is appended.
> If
> the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
>
> The simplified code comes down to the following when resetting the link
> without deleting the existing tabledef:
>
> tDef.Attributes = DB_ATTACHSAVEPWD
> tDef.Connect = NewConnectionString
> tDef.RefreshLink
>
> And the following when deleting the tabledef:
>
> DB.TableDefs.Delete (TableName)
> DB.TableDefs.Refresh
> tDef.Name = TableName
> tDef.Attributes = DB_ATTACHSAVEPWD
> tDef.SourceTableName = Owner & "." & TableName"
> DB.TableDefs.Append tDef (error: Could not find installable isam)
> DB.TableDefs(TableName).RefreshLink
>
> This code and connect strings works well when not changing to a differnet
> type of database.
>
> Note: I know this works because I've done it before in a different
> application but the same technique does not work in this database.
>
> Thanks in advance for your help.
>
>
> --
> RobGMiller
>
>
> "Douglas J. Steele" wrote:
>
>> Sorry, I can't think of other possible solutions.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
>> > My apologies for the late response. I had a high priority project
>> > due...
>> >
>> > I was successfully able to link and tried to copy the description in
>> > the
>> > table design, but got the same issue.
>> >
>> > -Mark
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> Try creating a linked table through File | Get External Data | Link
>> >> Tables.
>> >> Assuming that works, examine the Connect property of the table you
>> >> created.
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no private e-mails, please)
>> >>
>> >>
>> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
>> >> > Hi Doug,
>> >> >
>> >> > Tried that change and unfortunately still not working. Here's the
>> >> > full
>> >> > sub,
>> >> > with the password and user removed to protect the innocent. I tried
>> >> > "ODBC;"
>> >> > and "ODBC=;" and first one gave me another error, but the second one
>> >> > worked.
>> >> > I'm able to see the rs recordset, but appending the tdf tabledef
>> >> > causes
>> >> > that
>> >> > same ISAM issue.
>> >> >
>> >> > And thank you so much for your help.
>> >> > -Mark
>> >> >
>> >> >
>> >> > Sub ADOConnect()
>> >> > Dim rs As New ADODB.Recordset
>> >> > Dim db As Database
>> >> > Dim conn As ADODB.Connection
>> >> > Dim i As Integer, j As Integer
>> >> > Dim str As String
>> >> > Dim tdf As TableDef
>> >> >
>> >> > Set conn = New ADODB.Connection
>> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
>> >> >
>> >> > conn.ConnectionString = str
>> >> > conn.Open
>> >> >
>> >> > Set db = CurrentDb
>> >> > Set tdf = db.CreateTableDef("TEST")
>> >> > tdf.Connect = str
>> >> > tdf.SourceTableName = "PINSQUERY"
>> >> >
>> >> > 'rs opens okay with the connection.
>> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
>> >> > adLockReadOnly
>> >> > Debug.Print rs(0).Name
>> >> > Debug.Print rs(0).Value
>> >> >
>> >> > 'errors out here.
>> >> > db.TableDefs.Append tdf
>> >> >
>> >> > End Sub
>> >> >
>> >> >
>> >> > "Douglas J. Steele" wrote:
>> >> >
>> >> >> I must have made a typo. That was supposed to be a semi-colon
>> >> >> between
>> >> >> ODBC
>> >> >> and DSN=.
>> >> >>
>> >> >> Sorry about that.
>> >> >>
>> >> >> --
>> >> >> Doug Steele, Microsoft Access MVP
>> >> >> http://I.Am/DougSteele
>> >> >> (no e-mails, please!)
>> >> >>
>> >> >>
>> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
>> >> >> > Hi Doug,
>> >> >> >
>> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
>> >> >> > following
>> >> >> > your
>> >> >> > suggestion correctly. I updated str so
>> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
>> >> >> >
>> >> >> > where the character between the ODBC and DSN is a pipe (it copies
>> >> >> > as
>> >> >> > the
>> >> >> > letter 'l'). I didn't change anything else in the code, but am
>> >> >> > still
>> >> >> > getting
>> >> >> > the same error. Was there something else I needed to change?
>> >> >> >
>> >> >> > Oh, and yes, i'm using ADO.
>> >> >> >
>> >> >> > Thanks for taking my call!
>> >> >> > -Mark
>> >> >> >
>> >> >> >
>> >> >> > "Douglas J. Steele" wrote:
>> >> >> >
>> >> >> >> I'm assuming that your recordset is using ADO.
>> >> >> >>
>> >> >> >> What you've got appears to be an Ole DB connection string
>> >> >> >> (actually,
>> >> >> >> the
>> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
>> >> >> >> replacements, see what Carl Prothman has at
>> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
>> >> >> >>
>> >> >> >> When creating linked tables and/or pass-through queries, your
>> >> >> >> only
>> >> >> >> choice
>> >> >> >> is
>> >> >> >> ODBC. Since you apparently have a DSN created, try:
>> >> >> >>
>> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> http://I.Am/DougSteele
>> >> >> >> (no e-mails, please!)
>> >> >> >>
>> >> >> >>
>> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > I'm using Access 2003 and trying to create a linked table from
>> >> >> >> > an
>> >> >> >> > AS400
>> >> >> >> > database.
>> >> >> >> >
>> >> >> >> > Here's what I have that's not working.
>> >> >> >> > ===========
>> >> >> >> > Dim db As Database
>> >> >> >> > Dim tdf As TableDef
>> >> >> >> > dim str as string
>> >> >> >> >
>> >> >> >> > str =
>> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
>> >> >> >> >
>> >> >> >> > Set tdf = db.CreateTableDef("TEST")
>> >> >> >> > tdf.Connect = str
>> >> >> >> > tdf.SourceTableName = "TBL1"
>> >> >> >> > db.TableDefs.Append tdf
>> >> >> >> > ===========
>> >> >> >> >
>> >> >> >> > When I get to the last line, I get the error "could not find
>> >> >> >> > installable
>> >> >> >> > ISAM"
>> >> >> >> >
>> >> >> >> > Now, two things
>> >> >> >> > 1) if i use the same connectiong string (str) and open up a
>> >> >> >> > recordset
>> >> >> >> > object
>> >> >> >> > to TBL1, I can successfully get to that table, so that tells
>> >> >> >> > me
>> >> >> >> > the
>> >> >> >> > connection string is okay.
>> >> >> >> > 2) If I put in the wrong password in the connection string,
>> >> >> >> > tdf.connect
>> >> >> >> > doesn't give any error.
>> >> >> >> >
>> >> >> >> > Okay, any ideas how I can fix this?
>> >> >> >> >
>> >> >> >> > Thanks!
>> >> >> >> > -Mark
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

== 2 of 9 ==
Date: Wed, Nov 14 2007 12:26 pm
From: "Pieter Wijnen"

ODBC won't wash with ADO, I think
You need to use DAO to link, ah well, DAO tables (as you discovered)
ADOX is probably an alternative, but...

Pieter

== 3 of 9 ==
Date: Wed, Nov 14 2007 12:51 pm
From: RobGMiller

Thanks for your quick reply Doug,

If I link the table manually to SQL Server the SourceTableName is
dbo.tablename. I hard coded the full name in the new tabledef either with the
same case and with all upper case. I get the same result.

--
RobGMiller

"Douglas J. Steele" wrote:

> Check that the SourceTableName property is correct. If you're changing from
> linking to Jet vs. linking to SQL Server, your SourceTableName will be
> missing the owner (dbo.TableName, as opposed to TableName)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
> >I am having a similar problem linking tables to SQLServer or a different
> > access database. In my case, I get the ISAM error when I try to change the
> > tabledef.connect from one to the other.
> >
> > The connection strings I use work if I am not changing from one type to
> > another.
> >
> > ACCESS:
> > ;Database=O:\FullPath\databasename.mdb
> >
> > SQL
> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> > Info=True
> >
> > So I can change to a different access database or I can change to a
> > different SQL server or database in the same server.
> >
> > I've looked at Doug's code on
> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried other
> > methods.
> >
> > Id doesn't matter if the table to be changed is deleted first or not. If
> > the
> > tabledef is deleted the ISAM error occurs as the new tabledef is appended.
> > If
> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
> >
> > The simplified code comes down to the following when resetting the link
> > without deleting the existing tabledef:
> >
> > tDef.Attributes = DB_ATTACHSAVEPWD
> > tDef.Connect = NewConnectionString
> > tDef.RefreshLink
> >
> > And the following when deleting the tabledef:
> >
> > DB.TableDefs.Delete (TableName)
> > DB.TableDefs.Refresh
> > tDef.Name = TableName
> > tDef.Attributes = DB_ATTACHSAVEPWD
> > tDef.SourceTableName = Owner & "." & TableName"
> > DB.TableDefs.Append tDef (error: Could not find installable isam)
> > DB.TableDefs(TableName).RefreshLink
> >
> > This code and connect strings works well when not changing to a differnet
> > type of database.
> >
> > Note: I know this works because I've done it before in a different
> > application but the same technique does not work in this database.
> >
> > Thanks in advance for your help.
> >
> >
> > --
> > RobGMiller
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Sorry, I can't think of other possible solutions.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> >> > My apologies for the late response. I had a high priority project
> >> > due...
> >> >
> >> > I was successfully able to link and tried to copy the description in
> >> > the
> >> > table design, but got the same issue.
> >> >
> >> > -Mark
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> Try creating a linked table through File | Get External Data | Link
> >> >> Tables.
> >> >> Assuming that works, examine the Connect property of the table you
> >> >> created.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> >> >> > Hi Doug,
> >> >> >
> >> >> > Tried that change and unfortunately still not working. Here's the
> >> >> > full
> >> >> > sub,
> >> >> > with the password and user removed to protect the innocent. I tried
> >> >> > "ODBC;"
> >> >> > and "ODBC=;" and first one gave me another error, but the second one
> >> >> > worked.
> >> >> > I'm able to see the rs recordset, but appending the tdf tabledef
> >> >> > causes
> >> >> > that
> >> >> > same ISAM issue.
> >> >> >
> >> >> > And thank you so much for your help.
> >> >> > -Mark
> >> >> >
> >> >> >
> >> >> > Sub ADOConnect()
> >> >> > Dim rs As New ADODB.Recordset
> >> >> > Dim db As Database
> >> >> > Dim conn As ADODB.Connection
> >> >> > Dim i As Integer, j As Integer
> >> >> > Dim str As String
> >> >> > Dim tdf As TableDef
> >> >> >
> >> >> > Set conn = New ADODB.Connection
> >> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> >> >> >
> >> >> > conn.ConnectionString = str
> >> >> > conn.Open
> >> >> >
> >> >> > Set db = CurrentDb
> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> > tdf.Connect = str
> >> >> > tdf.SourceTableName = "PINSQUERY"
> >> >> >
> >> >> > 'rs opens okay with the connection.
> >> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> >> >> > adLockReadOnly
> >> >> > Debug.Print rs(0).Name
> >> >> > Debug.Print rs(0).Value
> >> >> >
> >> >> > 'errors out here.
> >> >> > db.TableDefs.Append tdf
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > "Douglas J. Steele" wrote:
> >> >> >
> >> >> >> I must have made a typo. That was supposed to be a semi-colon
> >> >> >> between
> >> >> >> ODBC
> >> >> >> and DSN=.
> >> >> >>
> >> >> >> Sorry about that.
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no e-mails, please!)
> >> >> >>
> >> >> >>
> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> >> >> >> > Hi Doug,
> >> >> >> >
> >> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> >> >> >> > following
> >> >> >> > your
> >> >> >> > suggestion correctly. I updated str so
> >> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >
> >> >> >> > where the character between the ODBC and DSN is a pipe (it copies
> >> >> >> > as
> >> >> >> > the
> >> >> >> > letter 'l'). I didn't change anything else in the code, but am
> >> >> >> > still
> >> >> >> > getting
> >> >> >> > the same error. Was there something else I needed to change?
> >> >> >> >
> >> >> >> > Oh, and yes, i'm using ADO.
> >> >> >> >
> >> >> >> > Thanks for taking my call!
> >> >> >> > -Mark
> >> >> >> >
> >> >> >> >
> >> >> >> > "Douglas J. Steele" wrote:
> >> >> >> >
> >> >> >> >> I'm assuming that your recordset is using ADO.
> >> >> >> >>
> >> >> >> >> What you've got appears to be an Ole DB connection string
> >> >> >> >> (actually,
> >> >> >> >> the
> >> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
> >> >> >> >> replacements, see what Carl Prothman has at
> >> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> >> >> >> >>
> >> >> >> >> When creating linked tables and/or pass-through queries, your
> >> >> >> >> only
> >> >> >> >> choice
> >> >> >> >> is
> >> >> >> >> ODBC. Since you apparently have a DSN created, try:
> >> >> >> >>
> >> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> >> http://I.Am/DougSteele
> >> >> >> >> (no e-mails, please!)
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> >> >> >> >> > Hi,
> >> >> >> >> >
> >> >> >> >> > I'm using Access 2003 and trying to create a linked table from
> >> >> >> >> > an
> >> >> >> >> > AS400
> >> >> >> >> > database.
> >> >> >> >> >
> >> >> >> >> > Here's what I have that's not working.
> >> >> >> >> > ===========
> >> >> >> >> > Dim db As Database
> >> >> >> >> > Dim tdf As TableDef
> >> >> >> >> > dim str as string
> >> >> >> >> >
> >> >> >> >> > str =
> >> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> >> >> >> >> >
> >> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> >> > tdf.Connect = str
> >> >> >> >> > tdf.SourceTableName = "TBL1"
> >> >> >> >> > db.TableDefs.Append tdf
> >> >> >> >> > ===========
> >> >> >> >> >
> >> >> >> >> > When I get to the last line, I get the error "could not find
> >> >> >> >> > installable
> >> >> >> >> > ISAM"
> >> >> >> >> >
> >> >> >> >> > Now, two things
> >> >> >> >> > 1) if i use the same connectiong string (str) and open up a
> >> >> >> >> > recordset
> >> >> >> >> > object
> >> >> >> >> > to TBL1, I can successfully get to that table, so that tells
> >> >> >> >> > me
> >> >> >> >> > the
> >> >> >> >> > connection string is okay.
> >> >> >> >> > 2) If I put in the wrong password in the connection string,
> >> >> >> >> > tdf.connect
> >> >> >> >> > doesn't give any error.
> >> >> >> >> >
> >> >> >> >> > Okay, any ideas how I can fix this?
> >> >> >> >> >
> >> >> >> >> > Thanks!
> >> >> >> >> > -Mark
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

== 4 of 9 ==
Date: Wed, Nov 14 2007 1:06 pm
From: RobGMiller

Thanks for the quick reply Pieter,

As far a I understand, its all DAO.

I suppose I could have posted the initialization code as follows:

Dim DB As DAO.Database
Dim tDef As DAO.TableDef

Set DB as CurrentDb()

--
RobGMiller

"Pieter Wijnen" wrote:

> ODBC won't wash with ADO, I think
> You need to use DAO to link, ah well, DAO tables (as you discovered)
> ADOX is probably an alternative, but...
>
> Pieter
>
> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
> >I am having a similar problem linking tables to SQLServer or a different
> > access database. In my case, I get the ISAM error when I try to change the
> > tabledef.connect from one to the other.
> >
> > The connection strings I use work if I am not changing from one type to
> > another.
> >
> > ACCESS:
> > ;Database=O:\FullPath\databasename.mdb
> >
> > SQL
> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> > Info=True
> >
> > So I can change to a different access database or I can change to a
> > different SQL server or database in the same server.
> >
> > I've looked at Doug's code on
> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried other
> > methods.
> >
> > Id doesn't matter if the table to be changed is deleted first or not. If
> > the
> > tabledef is deleted the ISAM error occurs as the new tabledef is appended.
> > If
> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
> >
> > The simplified code comes down to the following when resetting the link
> > without deleting the existing tabledef:
> >
> > tDef.Attributes = DB_ATTACHSAVEPWD
> > tDef.Connect = NewConnectionString
> > tDef.RefreshLink
> >
> > And the following when deleting the tabledef:
> >
> > DB.TableDefs.Delete (TableName)
> > DB.TableDefs.Refresh
> > tDef.Name = TableName
> > tDef.Attributes = DB_ATTACHSAVEPWD
> > tDef.SourceTableName = Owner & "." & TableName"
> > DB.TableDefs.Append tDef (error: Could not find installable isam)
> > DB.TableDefs(TableName).RefreshLink
> >
> > This code and connect strings works well when not changing to a differnet
> > type of database.
> >
> > Note: I know this works because I've done it before in a different
> > application but the same technique does not work in this database.
> >
> > Thanks in advance for your help.
> >
> >
> > --
> > RobGMiller
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Sorry, I can't think of other possible solutions.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> >> > My apologies for the late response. I had a high priority project
> >> > due...
> >> >
> >> > I was successfully able to link and tried to copy the description in
> >> > the
> >> > table design, but got the same issue.
> >> >
> >> > -Mark
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> Try creating a linked table through File | Get External Data | Link
> >> >> Tables.
> >> >> Assuming that works, examine the Connect property of the table you
> >> >> created.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> >> >> > Hi Doug,
> >> >> >
> >> >> > Tried that change and unfortunately still not working. Here's the
> >> >> > full
> >> >> > sub,
> >> >> > with the password and user removed to protect the innocent. I tried
> >> >> > "ODBC;"
> >> >> > and "ODBC=;" and first one gave me another error, but the second one
> >> >> > worked.
> >> >> > I'm able to see the rs recordset, but appending the tdf tabledef
> >> >> > causes
> >> >> > that
> >> >> > same ISAM issue.
> >> >> >
> >> >> > And thank you so much for your help.
> >> >> > -Mark
> >> >> >
> >> >> >
> >> >> > Sub ADOConnect()
> >> >> > Dim rs As New ADODB.Recordset
> >> >> > Dim db As Database
> >> >> > Dim conn As ADODB.Connection
> >> >> > Dim i As Integer, j As Integer
> >> >> > Dim str As String
> >> >> > Dim tdf As TableDef
> >> >> >
> >> >> > Set conn = New ADODB.Connection
> >> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> >> >> >
> >> >> > conn.ConnectionString = str
> >> >> > conn.Open
> >> >> >
> >> >> > Set db = CurrentDb
> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> > tdf.Connect = str
> >> >> > tdf.SourceTableName = "PINSQUERY"
> >> >> >
> >> >> > 'rs opens okay with the connection.
> >> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> >> >> > adLockReadOnly
> >> >> > Debug.Print rs(0).Name
> >> >> > Debug.Print rs(0).Value
> >> >> >
> >> >> > 'errors out here.
> >> >> > db.TableDefs.Append tdf
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > "Douglas J. Steele" wrote:
> >> >> >
> >> >> >> I must have made a typo. That was supposed to be a semi-colon
> >> >> >> between
> >> >> >> ODBC
> >> >> >> and DSN=.
> >> >> >>
> >> >> >> Sorry about that.
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no e-mails, please!)
> >> >> >>
> >> >> >>
> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> >> >> >> > Hi Doug,
> >> >> >> >
> >> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> >> >> >> > following
> >> >> >> > your
> >> >> >> > suggestion correctly. I updated str so
> >> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >
> >> >> >> > where the character between the ODBC and DSN is a pipe (it copies
> >> >> >> > as
> >> >> >> > the
> >> >> >> > letter 'l'). I didn't change anything else in the code, but am
> >> >> >> > still
> >> >> >> > getting
> >> >> >> > the same error. Was there something else I needed to change?
> >> >> >> >
> >> >> >> > Oh, and yes, i'm using ADO.
> >> >> >> >
> >> >> >> > Thanks for taking my call!
> >> >> >> > -Mark
> >> >> >> >
> >> >> >> >
> >> >> >> > "Douglas J. Steele" wrote:
> >> >> >> >
> >> >> >> >> I'm assuming that your recordset is using ADO.
> >> >> >> >>
> >> >> >> >> What you've got appears to be an Ole DB connection string
> >> >> >> >> (actually,
> >> >> >> >> the
> >> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
> >> >> >> >> replacements, see what Carl Prothman has at
> >> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> >> >> >> >>
> >> >> >> >> When creating linked tables and/or pass-through queries, your
> >> >> >> >> only
> >> >> >> >> choice
> >> >> >> >> is
> >> >> >> >> ODBC. Since you apparently have a DSN created, try:
> >> >> >> >>
> >> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> >> http://I.Am/DougSteele
> >> >> >> >> (no e-mails, please!)
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> >> >> >> >> > Hi,
> >> >> >> >> >
> >> >> >> >> > I'm using Access 2003 and trying to create a linked table from
> >> >> >> >> > an
> >> >> >> >> > AS400
> >> >> >> >> > database.
> >> >> >> >> >
> >> >> >> >> > Here's what I have that's not working.
> >> >> >> >> > ===========
> >> >> >> >> > Dim db As Database
> >> >> >> >> > Dim tdf As TableDef
> >> >> >> >> > dim str as string
> >> >> >> >> >
> >> >> >> >> > str =
> >> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> >> >> >> >> >
> >> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> >> > tdf.Connect = str
> >> >> >> >> > tdf.SourceTableName = "TBL1"
> >> >> >> >> > db.TableDefs.Append tdf
> >> >> >> >> > ===========
> >> >> >> >> >
> >> >> >> >> > When I get to the last line, I get the error "could not find
> >> >> >> >> > installable
> >> >> >> >> > ISAM"
> >> >> >> >> >
> >> >> >> >> > Now, two things
> >> >> >> >> > 1) if i use the same connectiong string (str) and open up a
> >> >> >> >> > recordset
> >> >> >> >> > object
> >> >> >> >> > to TBL1, I can successfully get to that table, so that tells
> >> >> >> >> > me
> >> >> >> >> > the
> >> >> >> >> > connection string is okay.
> >> >> >> >> > 2) If I put in the wrong password in the connection string,
> >> >> >> >> > tdf.connect
> >> >> >> >> > doesn't give any error.
> >> >> >> >> >
> >> >> >> >> > Okay, any ideas how I can fix this?
> >> >> >> >> >
> >> >> >> >> > Thanks!
> >> >> >> >> > -Mark
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

== 5 of 9 ==
Date: Wed, Nov 14 2007 1:53 pm
From: "Pieter Wijnen"

You need to include the table name in the link
Something like:

Const pODBCConnect = "ODBC;Driver={SQL Native Client};
Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security
Info=True"
'....
TDef.Connect = pODBCConnect & ";Table=dbo." & TDef.Name
'......

HtH

Pieter

"RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
news:B7D6215B-C931-4AD5-938C-3D743E705E40@microsoft.com...
> Thanks for the quick reply Pieter,
>
> As far a I understand, its all DAO.
>
> I suppose I could have posted the initialization code as follows:
>
> Dim DB As DAO.Database
> Dim tDef As DAO.TableDef
>
> Set DB as CurrentDb()
>
>
>
> --
> RobGMiller
>
>
> "Pieter Wijnen" wrote:
>
>> ODBC won't wash with ADO, I think
>> You need to use DAO to link, ah well, DAO tables (as you discovered)
>> ADOX is probably an alternative, but...
>>
>> Pieter
>>
>> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
>> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
>> >I am having a similar problem linking tables to SQLServer or a different
>> > access database. In my case, I get the ISAM error when I try to change
>> > the
>> > tabledef.connect from one to the other.
>> >
>> > The connection strings I use work if I am not changing from one type to
>> > another.
>> >
>> > ACCESS:
>> > ;Database=O:\FullPath\databasename.mdb
>> >
>> > SQL
>> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
>> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
>> > Info=True
>> >
>> > So I can change to a different access database or I can change to a
>> > different SQL server or database in the same server.
>> >
>> > I've looked at Doug's code on
>> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried
>> > other
>> > methods.
>> >
>> > Id doesn't matter if the table to be changed is deleted first or not.
>> > If
>> > the
>> > tabledef is deleted the ISAM error occurs as the new tabledef is
>> > appended.
>> > If
>> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
>> >
>> > The simplified code comes down to the following when resetting the link
>> > without deleting the existing tabledef:
>> >
>> > tDef.Attributes = DB_ATTACHSAVEPWD
>> > tDef.Connect = NewConnectionString
>> > tDef.RefreshLink
>> >
>> > And the following when deleting the tabledef:
>> >
>> > DB.TableDefs.Delete (TableName)
>> > DB.TableDefs.Refresh
>> > tDef.Name = TableName
>> > tDef.Attributes = DB_ATTACHSAVEPWD
>> > tDef.SourceTableName = Owner & "." & TableName"
>> > DB.TableDefs.Append tDef (error: Could not find installable isam)
>> > DB.TableDefs(TableName).RefreshLink
>> >
>> > This code and connect strings works well when not changing to a
>> > differnet
>> > type of database.
>> >
>> > Note: I know this works because I've done it before in a different
>> > application but the same technique does not work in this database.
>> >
>> > Thanks in advance for your help.
>> >
>> >
>> > --
>> > RobGMiller
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> Sorry, I can't think of other possible solutions.
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
>> >> > My apologies for the late response. I had a high priority project
>> >> > due...
>> >> >
>> >> > I was successfully able to link and tried to copy the description in
>> >> > the
>> >> > table design, but got the same issue.
>> >> >
>> >> > -Mark
>> >> >
>> >> >
>> >> > "Douglas J. Steele" wrote:
>> >> >
>> >> >> Try creating a linked table through File | Get External Data | Link
>> >> >> Tables.
>> >> >> Assuming that works, examine the Connect property of the table you
>> >> >> created.
>> >> >>
>> >> >> --
>> >> >> Doug Steele, Microsoft Access MVP
>> >> >> http://I.Am/DougSteele
>> >> >> (no private e-mails, please)
>> >> >>
>> >> >>
>> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
>> >> >> > Hi Doug,
>> >> >> >
>> >> >> > Tried that change and unfortunately still not working. Here's the
>> >> >> > full
>> >> >> > sub,
>> >> >> > with the password and user removed to protect the innocent. I
>> >> >> > tried
>> >> >> > "ODBC;"
>> >> >> > and "ODBC=;" and first one gave me another error, but the second
>> >> >> > one
>> >> >> > worked.
>> >> >> > I'm able to see the rs recordset, but appending the tdf tabledef
>> >> >> > causes
>> >> >> > that
>> >> >> > same ISAM issue.
>> >> >> >
>> >> >> > And thank you so much for your help.
>> >> >> > -Mark
>> >> >> >
>> >> >> >
>> >> >> > Sub ADOConnect()
>> >> >> > Dim rs As New ADODB.Recordset
>> >> >> > Dim db As Database
>> >> >> > Dim conn As ADODB.Connection
>> >> >> > Dim i As Integer, j As Integer
>> >> >> > Dim str As String
>> >> >> > Dim tdf As TableDef
>> >> >> >
>> >> >> > Set conn = New ADODB.Connection
>> >> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
>> >> >> >
>> >> >> > conn.ConnectionString = str
>> >> >> > conn.Open
>> >> >> >
>> >> >> > Set db = CurrentDb
>> >> >> > Set tdf = db.CreateTableDef("TEST")
>> >> >> > tdf.Connect = str
>> >> >> > tdf.SourceTableName = "PINSQUERY"
>> >> >> >
>> >> >> > 'rs opens okay with the connection.
>> >> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
>> >> >> > adLockReadOnly
>> >> >> > Debug.Print rs(0).Name
>> >> >> > Debug.Print rs(0).Value
>> >> >> >
>> >> >> > 'errors out here.
>> >> >> > db.TableDefs.Append tdf
>> >> >> >
>> >> >> > End Sub
>> >> >> >
>> >> >> >
>> >> >> > "Douglas J. Steele" wrote:
>> >> >> >
>> >> >> >> I must have made a typo. That was supposed to be a semi-colon
>> >> >> >> between
>> >> >> >> ODBC
>> >> >> >> and DSN=.
>> >> >> >>
>> >> >> >> Sorry about that.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> http://I.Am/DougSteele
>> >> >> >> (no e-mails, please!)
>> >> >> >>
>> >> >> >>
>> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
>> >> >> >> > Hi Doug,
>> >> >> >> >
>> >> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
>> >> >> >> > following
>> >> >> >> > your
>> >> >> >> > suggestion correctly. I updated str so
>> >> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
>> >> >> >> >
>> >> >> >> > where the character between the ODBC and DSN is a pipe (it
>> >> >> >> > copies
>> >> >> >> > as
>> >> >> >> > the
>> >> >> >> > letter 'l'). I didn't change anything else in the code, but am
>> >> >> >> > still
>> >> >> >> > getting
>> >> >> >> > the same error. Was there something else I needed to change?
>> >> >> >> >
>> >> >> >> > Oh, and yes, i'm using ADO.
>> >> >> >> >
>> >> >> >> > Thanks for taking my call!
>> >> >> >> > -Mark
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Douglas J. Steele" wrote:
>> >> >> >> >
>> >> >> >> >> I'm assuming that your recordset is using ADO.
>> >> >> >> >>
>> >> >> >> >> What you've got appears to be an Ole DB connection string
>> >> >> >> >> (actually,
>> >> >> >> >> the
>> >> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft:
>> >> >> >> >> for
>> >> >> >> >> replacements, see what Carl Prothman has at
>> >> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
>> >> >> >> >>
>> >> >> >> >> When creating linked tables and/or pass-through queries, your
>> >> >> >> >> only
>> >> >> >> >> choice
>> >> >> >> >> is
>> >> >> >> >> ODBC. Since you apparently have a DSN created, try:
>> >> >> >> >>
>> >> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Doug Steele, Microsoft Access MVP
>> >> >> >> >> http://I.Am/DougSteele
>> >> >> >> >> (no e-mails, please!)
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
>> >> >> >> >> > Hi,
>> >> >> >> >> >
>> >> >> >> >> > I'm using Access 2003 and trying to create a linked table
>> >> >> >> >> > from
>> >> >> >> >> > an
>> >> >> >> >> > AS400
>> >> >> >> >> > database.
>> >> >> >> >> >
>> >> >> >> >> > Here's what I have that's not working.
>> >> >> >> >> > ===========
>> >> >> >> >> > Dim db As Database
>> >> >> >> >> > Dim tdf As TableDef
>> >> >> >> >> > dim str as string
>> >> >> >> >> >
>> >> >> >> >> > str =
>> >> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
>> >> >> >> >> >
>> >> >> >> >> > Set tdf = db.CreateTableDef("TEST")
>> >> >> >> >> > tdf.Connect = str
>> >> >> >> >> > tdf.SourceTableName = "TBL1"
>> >> >> >> >> > db.TableDefs.Append tdf
>> >> >> >> >> > ===========
>> >> >> >> >> >
>> >> >> >> >> > When I get to the last line, I get the error "could not
>> >> >> >> >> > find
>> >> >> >> >> > installable
>> >> >> >> >> > ISAM"
>> >> >> >> >> >
>> >> >> >> >> > Now, two things
>> >> >> >> >> > 1) if i use the same connectiong string (str) and open up a
>> >> >> >> >> > recordset
>> >> >> >> >> > object
>> >> >> >> >> > to TBL1, I can successfully get to that table, so that
>> >> >> >> >> > tells
>> >> >> >> >> > me
>> >> >> >> >> > the
>> >> >> >> >> > connection string is okay.
>> >> >> >> >> > 2) If I put in the wrong password in the connection string,
>> >> >> >> >> > tdf.connect
>> >> >> >> >> > doesn't give any error.
>> >> >> >> >> >
>> >> >> >> >> > Okay, any ideas how I can fix this?
>> >> >> >> >> >
>> >> >> >> >> > Thanks!
>> >> >> >> >> > -Mark
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

== 6 of 9 ==
Date: Wed, Nov 14 2007 2:29 pm
From: RobGMiller

Just to clarify, as a general comment about the missing ISAM.

Can we assume that if the tables can be linked manually or the link can be
updated programmatically without changing the linked database type, then the
ISAM is present and healthy?

If that is the case, then, another way to look at it would be that a table
cannot be created from scratch in this database with the same connect string
that will work to change an existing similar tabledef.connect string.

To explore that outlook on the problem I was able to create a table from
scratch using the same connect string that is used when the table is first
linked manually. Then I was able to change that connect string to the one I
want without an error.

I guess we have a solution except for one thing; The manual connect string
is using a ODBC Machine DSN and I dont really want to have to install a DSN
on every computer using this application. So my choice is to learn how to
create the correct Machine DSN programatically or figure out why the desired
connect string will not allow a tabledef to be created.

The SQL Server connect strings that will work to create a table
programmatically is:

"ODBC;DSN=EngManager;APP=Microsoft Office
2003;WSID=ROB-HOME;DATABASE=EngManager"

The DSN-less one that wont work is:

"Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security Info=True

I've trieed changing {SQL Native Client} to {SQL Server} and removing the
Persist Security Info=True

Thanks for the suggestions ...

--
RobGMiller

"Douglas J. Steele" wrote:

> Sorry, I can't think of other possible solutions.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> > My apologies for the late response. I had a high priority project due...
> >
> > I was successfully able to link and tried to copy the description in the
> > table design, but got the same issue.
> >
> > -Mark
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Try creating a linked table through File | Get External Data | Link
> >> Tables.
> >> Assuming that works, examine the Connect property of the table you
> >> created.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> >> > Hi Doug,
> >> >
> >> > Tried that change and unfortunately still not working. Here's the full
> >> > sub,
> >> > with the password and user removed to protect the innocent. I tried
> >> > "ODBC;"
> >> > and "ODBC=;" and first one gave me another error, but the second one
> >> > worked.
> >> > I'm able to see the rs recordset, but appending the tdf tabledef causes
> >> > that
> >> > same ISAM issue.
> >> >
> >> > And thank you so much for your help.
> >> > -Mark
> >> >
> >> >
> >> > Sub ADOConnect()
> >> > Dim rs As New ADODB.Recordset
> >> > Dim db As Database
> >> > Dim conn As ADODB.Connection
> >> > Dim i As Integer, j As Integer
> >> > Dim str As String
> >> > Dim tdf As TableDef
> >> >
> >> > Set conn = New ADODB.Connection
> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> >> >
> >> > conn.ConnectionString = str
> >> > conn.Open
> >> >
> >> > Set db = CurrentDb
> >> > Set tdf = db.CreateTableDef("TEST")
> >> > tdf.Connect = str
> >> > tdf.SourceTableName = "PINSQUERY"
> >> >
> >> > 'rs opens okay with the connection.
> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> >> > adLockReadOnly
> >> > Debug.Print rs(0).Name
> >> > Debug.Print rs(0).Value
> >> >
> >> > 'errors out here.
> >> > db.TableDefs.Append tdf
> >> >
> >> > End Sub
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> I must have made a typo. That was supposed to be a semi-colon between
> >> >> ODBC
> >> >> and DSN=.
> >> >>
> >> >> Sorry about that.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> >> >> > Hi Doug,
> >> >> >
> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> >> >> > following
> >> >> > your
> >> >> > suggestion correctly. I updated str so
> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >
> >> >> > where the character between the ODBC and DSN is a pipe (it copies as
> >> >> > the
> >> >> > letter 'l'). I didn't change anything else in the code, but am still
> >> >> > getting
> >> >> > the same error. Was there something else I needed to change?
> >> >> >
> >> >> > Oh, and yes, i'm using ADO.
> >> >> >
> >> >> > Thanks for taking my call!
> >> >> > -Mark
> >> >> >
> >> >> >
> >> >> > "Douglas J. Steele" wrote:
> >> >> >
> >> >> >> I'm assuming that your recordset is using ADO.
> >> >> >>
> >> >> >> What you've got appears to be an Ole DB connection string
> >> >> >> (actually,
> >> >> >> the
> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
> >> >> >> replacements, see what Carl Prothman has at
> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> >> >> >>
> >> >> >> When creating linked tables and/or pass-through queries, your only
> >> >> >> choice
> >> >> >> is
> >> >> >> ODBC. Since you apparently have a DSN created, try:
> >> >> >>
> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no e-mails, please!)
> >> >> >>
> >> >> >>
> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I'm using Access 2003 and trying to create a linked table from an
> >> >> >> > AS400
> >> >> >> > database.
> >> >> >> >
> >> >> >> > Here's what I have that's not working.
> >> >> >> > ===========
> >> >> >> > Dim db As Database
> >> >> >> > Dim tdf As TableDef
> >> >> >> > dim str as string
> >> >> >> >
> >> >> >> > str =
> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> >> >> >> >
> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> > tdf.Connect = str
> >> >> >> > tdf.SourceTableName = "TBL1"
> >> >> >> > db.TableDefs.Append tdf
> >> >> >> > ===========
> >> >> >> >
> >> >> >> > When I get to the last line, I get the error "could not find
> >> >> >> > installable
> >> >> >> > ISAM"
> >> >> >> >
> >> >> >> > Now, two things
> >> >> >> > 1) if i use the same connectiong string (str) and open up a
> >> >> >> > recordset
> >> >> >> > object
> >> >> >> > to TBL1, I can successfully get to that table, so that tells me
> >> >> >> > the
> >> >> >> > connection string is okay.
> >> >> >> > 2) If I put in the wrong password in the connection string,
> >> >> >> > tdf.connect
> >> >> >> > doesn't give any error.
> >> >> >> >
> >> >> >> > Okay, any ideas how I can fix this?
> >> >> >> >
> >> >> >> > Thanks!
> >> >> >> > -Mark
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

== 7 of 9 ==
Date: Wed, Nov 14 2007 2:31 pm
From: "Douglas J. Steele"

I don't see where you're setting the Connect property in your " the
following when deleting the tabledef:" code

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
news:7DBB455D-899B-4658-A480-4EA5EE09A3A0@microsoft.com...
> Thanks for your quick reply Doug,
>
> If I link the table manually to SQL Server the SourceTableName is
> dbo.tablename. I hard coded the full name in the new tabledef either with
> the
> same case and with all upper case. I get the same result.
>
>
>
> --
> RobGMiller
>
>
> "Douglas J. Steele" wrote:
>
>> Check that the SourceTableName property is correct. If you're changing
>> from
>> linking to Jet vs. linking to SQL Server, your SourceTableName will be
>> missing the owner (dbo.TableName, as opposed to TableName)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
>> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
>> >I am having a similar problem linking tables to SQLServer or a different
>> > access database. In my case, I get the ISAM error when I try to change
>> > the
>> > tabledef.connect from one to the other.
>> >
>> > The connection strings I use work if I am not changing from one type to
>> > another.
>> >
>> > ACCESS:
>> > ;Database=O:\FullPath\databasename.mdb
>> >
>> > SQL
>> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
>> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
>> > Info=True
>> >
>> > So I can change to a different access database or I can change to a
>> > different SQL server or database in the same server.
>> >
>> > I've looked at Doug's code on
>> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried
>> > other
>> > methods.
>> >
>> > Id doesn't matter if the table to be changed is deleted first or not.
>> > If
>> > the
>> > tabledef is deleted the ISAM error occurs as the new tabledef is
>> > appended.
>> > If
>> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
>> >
>> > The simplified code comes down to the following when resetting the link
>> > without deleting the existing tabledef:
>> >
>> > tDef.Attributes = DB_ATTACHSAVEPWD
>> > tDef.Connect = NewConnectionString
>> > tDef.RefreshLink
>> >
>> > And the following when deleting the tabledef:
>> >
>> > DB.TableDefs.Delete (TableName)
>> > DB.TableDefs.Refresh
>> > tDef.Name = TableName
>> > tDef.Attributes = DB_ATTACHSAVEPWD
>> > tDef.SourceTableName = Owner & "." & TableName"
>> > DB.TableDefs.Append tDef (error: Could not find installable isam)
>> > DB.TableDefs(TableName).RefreshLink
>> >
>> > This code and connect strings works well when not changing to a
>> > differnet
>> > type of database.
>> >
>> > Note: I know this works because I've done it before in a different
>> > application but the same technique does not work in this database.
>> >
>> > Thanks in advance for your help.

== 8 of 9 ==
Date: Wed, Nov 14 2007 2:40 pm
From: RobGMiller

Sorry to detract from that problem at hand with that omission. I do set it
using tDef.connect = "strung".

--
RobGMiller

"Douglas J. Steele" wrote:

> I don't see where you're setting the Connect property in your " the
> following when deleting the tabledef:" code
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> news:7DBB455D-899B-4658-A480-4EA5EE09A3A0@microsoft.com...
> > Thanks for your quick reply Doug,
> >
> > If I link the table manually to SQL Server the SourceTableName is
> > dbo.tablename. I hard coded the full name in the new tabledef either with
> > the
> > same case and with all upper case. I get the same result.
> >
> >
> >
> > --
> > RobGMiller
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Check that the SourceTableName property is correct. If you're changing
> >> from
> >> linking to Jet vs. linking to SQL Server, your SourceTableName will be
> >> missing the owner (dbo.TableName, as opposed to TableName)
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> >> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
> >> >I am having a similar problem linking tables to SQLServer or a different
> >> > access database. In my case, I get the ISAM error when I try to change
> >> > the
> >> > tabledef.connect from one to the other.
> >> >
> >> > The connection strings I use work if I am not changing from one type to
> >> > another.
> >> >
> >> > ACCESS:
> >> > ;Database=O:\FullPath\databasename.mdb
> >> >
> >> > SQL
> >> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> >> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> >> > Info=True
> >> >
> >> > So I can change to a different access database or I can change to a
> >> > different SQL server or database in the same server.
> >> >
> >> > I've looked at Doug's code on
> >> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried
> >> > other
> >> > methods.
> >> >
> >> > Id doesn't matter if the table to be changed is deleted first or not.
> >> > If
> >> > the
> >> > tabledef is deleted the ISAM error occurs as the new tabledef is
> >> > appended.
> >> > If
> >> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
> >> >
> >> > The simplified code comes down to the following when resetting the link
> >> > without deleting the existing tabledef:
> >> >
> >> > tDef.Attributes = DB_ATTACHSAVEPWD
> >> > tDef.Connect = NewConnectionString
> >> > tDef.RefreshLink
> >> >
> >> > And the following when deleting the tabledef:
> >> >
> >> > DB.TableDefs.Delete (TableName)
> >> > DB.TableDefs.Refresh
> >> > tDef.Name = TableName
> >> > tDef.Attributes = DB_ATTACHSAVEPWD
> >> > tDef.SourceTableName = Owner & "." & TableName"
> >> > DB.TableDefs.Append tDef (error: Could not find installable isam)
> >> > DB.TableDefs(TableName).RefreshLink
> >> >
> >> > This code and connect strings works well when not changing to a
> >> > differnet
> >> > type of database.
> >> >
> >> > Note: I know this works because I've done it before in a different
> >> > application but the same technique does not work in this database.
> >> >
> >> > Thanks in advance for your help.
>
>
>

== 9 of 9 ==
Date: Wed, Nov 14 2007 2:43 pm
From: RobGMiller

The table name seems to get added on automatically when updating the connect
string. I assumed that the SourceTableName property holds that value.
--
RobGMiller

"Pieter Wijnen" wrote:

> You need to include the table name in the link
> Something like:
>
> Const pODBCConnect = "ODBC;Driver={SQL Native Client};
> Server=BizTalkServer\OfficeServers;
> Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> Info=True"
> '....
> TDef.Connect = pODBCConnect & ";Table=dbo." & TDef.Name
> '......
>
> HtH
>
> Pieter
>
>
> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> news:B7D6215B-C931-4AD5-938C-3D743E705E40@microsoft.com...
> > Thanks for the quick reply Pieter,
> >
> > As far a I understand, its all DAO.
> >
> > I suppose I could have posted the initialization code as follows:
> >
> > Dim DB As DAO.Database
> > Dim tDef As DAO.TableDef
> >
> > Set DB as CurrentDb()
> >
> >
> >
> > --
> > RobGMiller
> >
> >
> > "Pieter Wijnen" wrote:
> >
> >> ODBC won't wash with ADO, I think
> >> You need to use DAO to link, ah well, DAO tables (as you discovered)
> >> ADOX is probably an alternative, but...
> >>
> >> Pieter
> >>
> >> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> >> news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@microsoft.com...
> >> >I am having a similar problem linking tables to SQLServer or a different
> >> > access database. In my case, I get the ISAM error when I try to change
> >> > the
> >> > tabledef.connect from one to the other.
> >> >
> >> > The connection strings I use work if I am not changing from one type to
> >> > another.
> >> >
> >> > ACCESS:
> >> > ;Database=O:\FullPath\databasename.mdb
> >> >
> >> > SQL
> >> > Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> >> > Database=EngManager;UID=limiteduser;PWD=password;Persist Security
> >> > Info=True
> >> >
> >> > So I can change to a different access database or I can change to a
> >> > different SQL server or database in the same server.
> >> >
> >> > I've looked at Doug's code on
> >> > http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried
> >> > other
> >> > methods.
> >> >
> >> > Id doesn't matter if the table to be changed is deleted first or not.
> >> > If
> >> > the
> >> > tabledef is deleted the ISAM error occurs as the new tabledef is
> >> > appended.
> >> > If
> >> > the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink
> >> >
> >> > The simplified code comes down to the following when resetting the link
> >> > without deleting the existing tabledef:
> >> >
> >> > tDef.Attributes = DB_ATTACHSAVEPWD
> >> > tDef.Connect = NewConnectionString
> >> > tDef.RefreshLink
> >> >
> >> > And the following when deleting the tabledef:
> >> >
> >> > DB.TableDefs.Delete (TableName)
> >> > DB.TableDefs.Refresh
> >> > tDef.Name = TableName
> >> > tDef.Attributes = DB_ATTACHSAVEPWD
> >> > tDef.SourceTableName = Owner & "." & TableName"
> >> > DB.TableDefs.Append tDef (error: Could not find installable isam)
> >> > DB.TableDefs(TableName).RefreshLink
> >> >
> >> > This code and connect strings works well when not changing to a
> >> > differnet
> >> > type of database.
> >> >
> >> > Note: I know this works because I've done it before in a different
> >> > application but the same technique does not work in this database.
> >> >
> >> > Thanks in advance for your help.
> >> >
> >> >
> >> > --
> >> > RobGMiller
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> Sorry, I can't think of other possible solutions.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> >> >> > My apologies for the late response. I had a high priority project
> >> >> > due...
> >> >> >
> >> >> > I was successfully able to link and tried to copy the description in
> >> >> > the
> >> >> > table design, but got the same issue.
> >> >> >
> >> >> > -Mark
> >> >> >
> >> >> >
> >> >> > "Douglas J. Steele" wrote:
> >> >> >
> >> >> >> Try creating a linked table through File | Get External Data | Link
> >> >> >> Tables.
> >> >> >> Assuming that works, examine the Connect property of the table you
> >> >> >> created.
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no private e-mails, please)
> >> >> >>
> >> >> >>
> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> >> >> >> > Hi Doug,
> >> >> >> >
> >> >> >> > Tried that change and unfortunately still not working. Here's the
> >> >> >> > full
> >> >> >> > sub,
> >> >> >> > with the password and user removed to protect the innocent. I
> >> >> >> > tried
> >> >> >> > "ODBC;"
> >> >> >> > and "ODBC=;" and first one gave me another error, but the second
> >> >> >> > one
> >> >> >> > worked.
> >> >> >> > I'm able to see the rs recordset, but appending the tdf tabledef
> >> >> >> > causes
> >> >> >> > that
> >> >> >> > same ISAM issue.
> >> >> >> >
> >> >> >> > And thank you so much for your help.
> >> >> >> > -Mark
> >> >> >> >
> >> >> >> >
> >> >> >> > Sub ADOConnect()
> >> >> >> > Dim rs As New ADODB.Recordset
> >> >> >> > Dim db As Database
> >> >> >> > Dim conn As ADODB.Connection
> >> >> >> > Dim i As Integer, j As Integer
> >> >> >> > Dim str As String
> >> >> >> > Dim tdf As TableDef
> >> >> >> >
> >> >> >> > Set conn = New ADODB.Connection
> >> >> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> >> >> >> >
> >> >> >> > conn.ConnectionString = str
> >> >> >> > conn.Open
> >> >> >> >
> >> >> >> > Set db = CurrentDb
> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> > tdf.Connect = str
> >> >> >> > tdf.SourceTableName = "PINSQUERY"
> >> >> >> >
> >> >> >> > 'rs opens okay with the connection.
> >> >> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> >> >> >> > adLockReadOnly
> >> >> >> > Debug.Print rs(0).Name
> >> >> >> > Debug.Print rs(0).Value
> >> >> >> >
> >> >> >> > 'errors out here.
> >> >> >> > db.TableDefs.Append tdf
> >> >> >> >
> >> >> >> > End Sub
> >> >> >> >
> >> >> >> >
> >> >> >> > "Douglas J. Steele" wrote:
> >> >> >> >
> >> >> >> >> I must have made a typo. That was supposed to be a semi-colon
> >> >> >> >> between
> >> >> >> >> ODBC
> >> >> >> >> and DSN=.
> >> >> >> >>
> >> >> >> >> Sorry about that.
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> >> http://I.Am/DougSteele
> >> >> >> >> (no e-mails, please!)
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> >> >> >> >> > Hi Doug,
> >> >> >> >> >
> >> >> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> >> >> >> >> > following
> >> >> >> >> > your
> >> >> >> >> > suggestion correctly. I updated str so
> >> >> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >> >
> >> >> >> >> > where the character between the ODBC and DSN is a pipe (it
> >> >> >> >> > copies
> >> >> >> >> > as
> >> >> >> >> > the
> >> >> >> >> > letter 'l'). I didn't change anything else in the code, but am
> >> >> >> >> > still
> >> >> >> >> > getting
> >> >> >> >> > the same error. Was there something else I needed to change?
> >> >> >> >> >
> >> >> >> >> > Oh, and yes, i'm using ADO.
> >> >> >> >> >
> >> >> >> >> > Thanks for taking my call!
> >> >> >> >> > -Mark
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > "Douglas J. Steele" wrote:
> >> >> >> >> >
> >> >> >> >> >> I'm assuming that your recordset is using ADO.
> >> >> >> >> >>
> >> >> >> >> >> What you've got appears to be an Ole DB connection string
> >> >> >> >> >> (actually,
> >> >> >> >> >> the
> >> >> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft:
> >> >> >> >> >> for
> >> >> >> >> >> replacements, see what Carl Prothman has at
> >> >> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> >> >> >> >> >>
> >> >> >> >> >> When creating linked tables and/or pass-through queries, your
> >> >> >> >> >> only
> >> >> >> >> >> choice
> >> >> >> >> >> is
> >> >> >> >> >> ODBC. Since you apparently have a DSN created, try:
> >> >> >> >> >>
> >> >> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> --
> >> >> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> >> >> http://I.Am/DougSteele
> >> >> >> >> >> (no e-mails, please!)
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> >> >> >> >> >> > Hi,
> >> >> >> >> >> >
> >> >> >> >> >> > I'm using Access 2003 and trying to create a linked table
> >> >> >> >> >> > from
> >> >> >> >> >> > an
> >> >> >> >> >> > AS400
> >> >> >> >> >> > database.
> >> >> >> >> >> >
> >> >> >> >> >> > Here's what I have that's not working.
> >> >> >> >> >> > ===========
> >> >> >> >> >> > Dim db As Database
> >> >> >> >> >> > Dim tdf As TableDef
> >> >> >> >> >> > dim str as string
> >> >> >> >> >> >
> >> >> >> >> >> > str =
> >> >> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> >> >> >> >> >> >
> >> >> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> >> >> > tdf.Connect = str
> >> >> >> >> >> > tdf.SourceTableName = "TBL1"
> >> >> >> >> >> > db.TableDefs.Append tdf
> >> >> >> >> >> > ===========
> >> >> >> >> >> >
> >> >> >> >> >> > When I get to the last line, I get the error "could not
> >> >> >> >> >> > find
> >> >> >> >> >> > installable
> >> >> >> >> >> > ISAM"
> >> >> >> >> >> >
> >> >> >> >> >> > Now, two things
> >> >> >> >> >> > 1) if i use the same connectiong string (str) and open up a
> >> >> >> >> >> > recordset
> >> >> >> >> >> > object
> >> >> >> >> >> > to TBL1, I can successfully get to that table, so that
> >> >> >> >> >> > tells
> >> >> >> >> >> > me

==============================================================================
TOPIC: Send Object Action
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e749b518a225b04a?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 12:49 pm
From: tpeter

I have a report that the user can e-mail to a general folder. I am using the
command button wizard and everything works fine, the only thing I would like
to change is the report is being picked up by outlook secturity saying
information is being automatically started. They then have the option to stop
the email or send it. When they click the e-mail report button I would like
for them to load in the project number they want to send and no other
options. Here is the code that I have any help would be great.

Private Sub Email_Report_Click()
On Error GoTo Err_Email_Report_Click

Dim stDocName As String
stDocName = "rptEmailReport"
DoCmd.SendObject acReport, stDocName, acFormatSNP, "PRIMARY PERSON GOING
TO", "SECONDARY PERSON GOING TO", , "Lab Request", , 0
Exit_Email_Report_Click:
Exit Sub

Err_Email_Report_Click:
MsgBox Err.Description
Resume Exit_Email_Report_Click

End Sub

==============================================================================
TOPIC: Is there an utility.wlib_AutoDial replacement?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/988f324f3ac928d8?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 14 2007 1:01 pm
From: Al

This utility lives inside utility.mda which is an old MS97 add-in.

Is there a replacement for this so we don't have to make VBA Object library
references to Utility.MDA?

I have an application that crashes more often than not because of this
Utility.

Thanks for you replies!!

== 2 of 3 ==
Date: Wed, Nov 14 2007 2:14 pm
From: "Stuart McCall"

"Al" <Al@discussions.microsoft.com> wrote in message
news:76E51032-61AD-4142-A344-7AA5EC5A2F35@microsoft.com...
> This utility lives inside utility.mda which is an old MS97 add-in.
>
> Is there a replacement for this so we don't have to make VBA Object
> library
> references to Utility.MDA?
>
> I have an application that crashes more often than not because of this
> Utility.
>
> Thanks for you replies!!

Try this. Place the following at the top of a module (underneath the option
declarations and before any procedure code) :

Declare Function TAPI_Make_Call Lib _
"tapi32.dll" Alias "tapiRequestMakeCall" _
(ByVal stNumber As String, _
ByVal stDummy1 As String, _
ByVal stDummy2 As String, _
ByVal stDummy3 As String) As Long

Then use it like this:

TAPI_Make_Call "123456789", "", "", ""

It's been quite a while since I used this, I have no modem on this machine,
and I'm unable to remember whether it requires a formatted number or not.
Play with it till it works...

== 3 of 3 ==
Date: Wed, Nov 14 2007 2:30 pm
From: "Stuart McCall"

If you can't get that to work, or you don't like the behaviour (I seem to
remember it pops a dialog, which you may not want to see), you could try
this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;148857

==============================================================================
TOPIC: Audit Log
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0c40ff55056ae894?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 1:25 pm
From: raja07

i am trying to create an audit report of only changed fields and i've looked
at the code provided here http://support.microsoft.com/default.aspx/kb/197592
but instead of returning just the changes i am getting all the fields on the
form those whose values have changed and those that didn't change. can
someone please help me to grab only those values that have been edited? i
have spent all day on this and i could really use some help. thanks
--
raj

== 2 of 2 ==
Date: Wed, Nov 14 2007 2:40 pm
From: NevilleT

Hi Raj
I created an audit some time ago. First I created two audit files. One is
for changed memo fields and the other for everything else. If you need to
cater for memo fields, you will need a memo field to store the before and
after. This means a very large file very quickly. I would consider two
files.

What you need to do is loop through the controls on the form and find which
one is changed. You can then write some information to the history file..
This is a cut and paste from my code so there are some other functions called
but you can probably get the drift. I pass a pile of information to the
function and you may want to handle it differently.

Post a message if you want more info.

Public Function funLogTrans(frm As Form, _
intKey As Integer, _
strFormName As String, _
strKeyName As String, _
Optional strOptional As String) _
As Boolean
' Frm is the form passing the information
' intKey is the value of the PK
' strFormName is the name of the form being
modified including full path for subforms
' strKeyName is the name of the Primary Key
field in the table e.g. "tblPeople.PersonNo"
' strOptional1 is the cancatenated descriptive
string.
Dim dbs As DAO.Database
Dim ctlCtrl As Control
Dim MyMsg As String
Dim strHist As String
Dim lngOldValue As Long
Dim lngNewValue As Long

' Loop through controls to find ones that changed
For Each ctlCtrl In frm.Controls
If (funActiveCtrl(ctlCtrl)) Then ' Check
it is an updateable control
If IsNoOldValue(ctlCtrl) = True Then ' Is
the oldvalue valid for this control
If ctlCtrl.Enabled = True Then ' Is
the control enabled.
If ((ctlCtrl.Value <> ctlCtrl.OldValue) _
Or (IsNull(ctlCtrl) And Not IsNull(ctlCtrl.OldValue)) _
Or (Not IsNull(ctlCtrl) And IsNull(ctlCtrl.OldValue)))
Then
lngNewValue = Len(IIf(IsNull(ctlCtrl), 0, ctlCtrl))
lngOldValue = Len(IIf(IsNull(ctlCtrl.OldValue), 0,
ctlCtrl.OldValue))
If lngOldValue > 255 Or lngNewValue > 255 Then
' If a memo, write to that table
strHist = "tblHistMemo"
' Memo table
Else
strHist = "tblHist"
' Non memo table
End If

' This function creates new history records
Call funAddHist(strHist, _
intKey, _
strFormName, _
strKeyName, _
ctlCtrl, _
strOptional)

' strHist = Select which table to
enter data into
' MyKey is the value of the PK
' strFormName is the name of the
form being modified including full path for subforms
' strKeyName is the name of the
Primary Key field in the table e.g. "tblPeople.PersonNo"
' ctlCtrl is the control that changed
' strOptional1 is the cancatenated
descriptive string
End If
End If
End If
End If
Next ctlCtrl

End Function

"raja07" wrote:

> i am trying to create an audit report of only changed fields and i've looked
> at the code provided here http://support.microsoft.com/default.aspx/kb/197592
> but instead of returning just the changes i am getting all the fields on the
> form those whose values have changed and those that didn't change. can
> someone please help me to grab only those values that have been edited? i
> have spent all day on this and i could really use some help. thanks
> --
> raj

==============================================================================
TOPIC: Correlation coefficent (CORREL in excel)
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/54b18330ca60e8ed?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 1:49 pm
From: "Wolfgang Kais"

Hello AJ.

"AJ" wrote:
> I would like to run a correlation coefficent in ms access against
> some tables. Can I do that in a query or do I need to create a VBA
> procedure. I have a sql server query that works but not sure how
> to incorporate it into access.
> It is the same as the CORREL function in excel.
>
> Does anyone have any examples?

Let's assume that you have a table (TheTable) with two columns X
and Y that contain the numbers you want to use in the calculation,
then the following access query will calculate the desired value:

SELECT (Avg([X]*[Y])-Avg([X])*Avg([Y]))/(StDevP([X])*StDevP([Y]))
AS CORREL FROM TheTable;

--
Regards,
Wolfgang

==============================================================================
TOPIC: excel cell value
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4c9e7db119fdea74?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 1:53 pm
From: John W. Vinson

On Wed, 14 Nov 2007 12:18:00 -0800, jnewl <jnewl@discussions.microsoft.com>
wrote:

>want to check for a specific value in col 1 and row 1. if that value is not
>there, will cancel the operation.
>
>how do i check for that value?
>
>tried this
>
>xlsheet.cells("1:1") = rowvalue
>
>If rowvalue = "code" Then
> GoTo contin
> Else
> GoTo Err_addl_update_Click
>End If
>contin:
>
>rowvalue is defined as a string
>
>get error msg that not valid construct
>
>thanks for your help

Aren't Excel cells indexed by letters in the columns, not numbers? Try "A:1".

If you're running this code in Excel, please post your questions in an Excel
newsgroup.

John W. Vinson [MVP]

==============================================================================
TOPIC: Button in form header
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f72483eb1388258f?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 2:46 pm
From: mcescher

Help, I must be missing something basic (pardon the pun)

Windows XP Pro, Access 2002

Created a new form, with some unbound text boxes in the form header,
and a couple of buttons.

cmdClose just closes the form with DoCmd.Close

When I click the other button I want to check the values of the text
boxes. They don't show up with the dropdown when I type "me." But the
controls from the detail section do show up in the dropdown list.

When I try to compile, it throws an error on "If Len(Me.txtUID & "") =
0 Then"
"Compile error: Method or data member now found"

Both the box and the button are in the header, and I have records that
show up in the detail section, which is set to show "Continuous Forms"

I've tried:
txtUID
Me.txtUID
Forms!frmcamISeriesMaint!txtUID

All with bad results. What am I doing wrong? I just want to test for
valid data in the form, and feed the values to another function that
I've already written.

Thanks so much for your time,
Chris M.

==============================================================================
TOPIC: method 'range' of object'_global' failed on XL sort
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3da41c6385ca6abc?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 2:44 pm
From: "ragtopcaddy via AccessMonster.com"

I am getting the error msg "method 'range' of object'_global' failed "
sporadically in my code. I read elsewhere not to use "With ObjXL", but
there's no explanation of why.

I get it off and on with this line:

With objXL
(lots of other code...)
.ActiveSheet.Range("A1:AO" & iRow).Select
'Here's where I get the error:
.Selection.Sort Key1:=Range("AK2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:
=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

I have also seen another strange error with something like "RPL" in it's text
at the same point in my code.

Thanks,

Bill R

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Rabu, 14 November 2007

24 new messages in 10 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya