Alright. Here's the scoop:

There's 2 MDB files. One is chock full o' tables.
The other is mostly VBA and forms, and was thrown together by, from what I can ascertain from the existing "programming", drunken squirrels with arthritis.

Now, the "forms" mdb file, which we'll call frontend.mdb for sake of clarity, had the form from which I'd LIKE to execute a SQL "INSERT INTO" command, using data from the form, and several other calculations.

the "tables" mdb file (we'll call it backend.mdb) has the table link to the DSN object for the MYSQL database.

I have constructed a SQL query that, although it works in the "immediate" window of VBA (when fed the proper data in the VALUES() area) and also is given the exact same data when run from a form (I did a MsgBox (strSQLQuery) just to see that the values were right) and.....
NOTHING HAPPENS.

No error message, no ding, no "uhoh!" no information, no nothin'.

This is actually how this little monstrosity behaves if I so much as misconstruct a msgbox, so that's not the surprising part.

What IS surprising is that I don't see the mySQL linked table if I try to, oh, say, map a textbox to a field in the database.

I know they've got some code somewhere to connect frontend.mdb to backend.mdb but it ain't exactly doing a good job of picking up on the new table, and I'm rapidly getting tired of guessing where they've squirreled it away sooo....

If ANYONE out there knows how to connect to a linked SQL table in ONE mdb file from in an onclick() event in ANOTHER mdb file and do a SQL type INSERT INTO command into it, I'd be appreciative.

I've even tried this (pseudocode):

docmd.RUNSQL ( "INSERT INTO mylinked_table ( fields, morefields, etc) IN 'backend.mbd' VALUES ( 'blah', 'blah blah', 'etc')" )

Note the "IN 'backend.mdb'" part.

Even THAT works fine from Frontend.mbd 's VBA "immediate" window, but it won't work in a click event.

If anyone out there can preserve my sanity, I'd much appreciate it. Thanks!

-DW

Yikes to linked tables.

I would use a provider statement and just bypass the backend and go right for the MySQL database, using ADO.

For example, I have a database front end with linked SQL Server tables for show, but all the Update and Instert events use the following sort of code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
etc

sSQL = "INSERT INTO Table(Fields), Values(values)"

Set cn = New ADODB.Connection
cn.Open "Provider..."

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

cn.Close

That does the trick for me.

Did that help at all? It doesn't resolve it the way you want it, but it works.

You can also set up a DSN and go right for that as well.



mmcdonal

Quick question:

for cn.open "provider..."

The program is stalling right there. I put a nice little collection of MsgBox items down the list to give me specific feedback of what's going on. (I.E. "SQL string built! Here it is: ......")

Well, it runs the one juuuuuust before this ("preparing to open provider"):

cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=www.oursqldomain.net;Port=3306;Data base=data_base;User=data_user; Password=abc123;Option=3;"

And I never see the MsgBox "Provider opened" I stuck right after it. Soooo theres a problem with this, but I can't spot it. Any thoughts? Thanks!

-Duffy


Thanks!

-DW