Update local table with Sybase ODBC data in update query

[ragtopcaddy via AccessMonster.com]

I need a couple of lines of code here.

I have a linked Access97 table tblAccess, and a linked Sybase table tblSybase,
in my Access97 db.

I use a key field to link them. When I run the update query on these 2 tables
to update a field in tblAccess, I get a popup to log into Sybase, which must
be remedied in order to automate the process. I tried inserting a connection
string: ODBC;DSN=[DSN name];UID=[login];PWD=[pwd];DATABASE=[dbase];, in the
appropriate query property, but then it can't find tblAccess. I am running
this query from VBA, so how do I get this thing working?

Thanks,

Bill Reed

--
Bill Reed

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

Message posted via AccessMonster.com

 

[ragtopcaddy via AccessMonster.com]

I found a suggestion and took a couple of lines of code out of it:

strConnect="ODBC;DSN=" & strDSN & ";UID=" & strLogin & ";PWD=" & strPwd &
";DATABASE=" & strDB & ";"
Set dbODBC = OpenDatabase("", False, False, strConnect)

It didn't work. There was no error, but I still got the login dialog after
running these 2 lines.

I need a couple of lines of code here.

I have a linked Access97 table tblAccess, and a linked Sybase table tblSybase,
in my Access97 db.

Bill Reed

--
Bill Reed

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

Message posted via AccessMonster.com

 

[Pieter Wijnen]

The easiest solution is to create a passthrough query with the Correct
connection string & open a recordset on that first

HtH

Pieter

 

[ragtopcaddy via AccessMonster.com]

Thanks Pieter,

That worked great with a stored query. How do I create this query in VBA?

Thanks,

Bill

The easiest solution is to create a passthrough query with the Correct
connection string & open a recordset on that first

HtH

Pieter

I found a suggestion and took a couple of lines of code out of it:

[quoted text clipped - 12 lines]

--
Bill Reed

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

Message posted via AccessMonster.com

 

[Pieter Wijnen]

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim Rs AS DAO.recordset
Set Db = Access.CurrentDb()
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,VBA.vbNullString)
'Set Qdef = Db.CreateQueryDef("ConnectToSybase",VBA.vbNullString)

Qdef.SQL = "SELECT 'X'"
Qdef.Connect="ODBC;...."
Set Rs = Qdef.OpenRecordset(DAO.dbOpenSnapshot)
Rs.Close : Set Rs = Nothing
Qdef.Close: Set Qdef = Nothing
Set Db = Nothing

HtH

Pieter

Thanks Pieter,

That worked great with a stored query. How do I create this query in VBA?

Thanks,

Bill

The easiest solution is to create a passthrough query with the Correct
connection string & open a recordset on that first

HtH

Pieter

I found a suggestion and took a couple of lines of code out of it:

[quoted text clipped - 12 lines]

Bill Reed

--
Bill Reed

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

Message posted via AccessMonster.com