December 12, 2008 at 12:30 pm
Howyadoin,
The front end I'm converting performs a payment posting macro whereby it creates a table called Balance_Due with a SELECT INTO query. I can't seem to get the query to create the table on the SQL Server back end, it always creates it on the local MDB.
This isn't so bad except that the last part of the posting macro is an update query that uses an INNER JOIN with the table that it created locally and the other table in SQL Server. Needless to say, the update query takes forever to complete, and I'm assuming that it would be faster if both tables were in the same db.
What's the correct syntax to get Access to perform the SELECT INTO on the back end?
Thanks!
-Mark
December 18, 2008 at 4:28 am
How many rows is it updating? the problem may be that the local table that is created does not have a primary key or index on it - which would cause the update to be slow.
How are the tables linked into access - through linked table manager or via .adp project?
If via linked tables then create the table 'Balance_Due' on the sql server database - then link it back into the access database. Make sure you define a primary key field (perhaps at the end so it does interfere with your insert statement - New Field: ID (column name) Int (as the datatype) in the properties options select Identity = Yes and Seed = 1 (this is the equivalent of AutoNumber in access i guess)
Take the create table step out of the macro - ensure the table name is correct in the macro (linked tables will often bring in 'dbo_Balance_Due' - you can simply rename the table 'Balance_Due' and the macro will work fine).
Oraculum
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply