Issue with transferring data to SQL (1 Viewer)

[mahie]

Hi all,
I am trying to transfer data from .mdb file to SQL server..
I tried linking the table using ODBC but I am not able to access it to insert records..

I created a DSN-Less connection and am able to connect to the SQL database.. however am getting error while I try to xfer the data..

Error 3170: Could not find installable ISAM.

My code is as below:
-------------------------------
stConnect1 = "OBDC;Driver={SQL Server Native Client 10.0} ;Server=" & stServer & ";Database=" & stDatabase & "; UID=" & stUsername & ";PWD=" & stPassword & ";"

Dim db As Database, tbldef As DAO.TableDef
'
Dbname = "amd_temp.accdb"
dd = "C:\Documents and Settings\MSC055\My Documents\Auto-Amd\" & Dbname
Set db = OpenDatabase(dd)
For Each tbldef In db.TableDefs
sXferTable = "Amd_SI_Det"
sTblNm = tbldef.Name
If sTblNm = "dbo_Amd_SI_Details_Temp" Then
Set td = db.CreateTableDef("newTable")
td.Connect = stConnect1
td.SourceTableName = "Amd_SI_Det"
db.TableDefs.Append td
End If
Next tbldef

------------------

Please help...

 

[mdlueck]

I am skeptical if such code will even work. It what you are trying to do create the tables on-the-fly and transfer the data records in the Access table to the SQL BE DB?

I have good success with having pre-created on the SQL BE DB the empty tables via DDL script, and then Access is able to successfully INSERT records to the empty table. I have one ADO.Recordset object bound to the Access table, reading the entire table, and each record read I create an ADO.Command object to send that one record to the SQL BE DB via calling the INSERT Stored Procedure. After the INSERT is executed, I make a call to DoEvents to keep the Access UI responsive.

 

[mahie]

Hi dear,
Before coming to this stage I did try writing a SP in SQL to OPENROWSET & OPENdatasource to get the data from Access & update into a table pre-created in SQL DB. but with that am getting another error which says
"
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
I cannot go with the approach of updating records one by one as I may have even 200-500 records/transaction.. and it will affect the time in which my tool will give output..

I also tried creating a linked table in my access table but am not able to access that table via my query to insert records..

Please help me am stuck with this past 3 days.

Regards,
Mahie

 

[mdlueck]

I cannot go with the approach of updating records one by one as I may have even 200-500 records/transaction.. and it will affect the time in which my tool will give output..

While I acknowledge that use of individual calls to a SP which performs a SQL INSERT is slower, at least it works reliably.

I know of no way to bulk transfer records from Access to a SQL BE DB reliably.

Perhaps using a non Pass-Through query to SELECT from the Access table and INSERT into the SQL BE DB table. You will need a Linked Table object in the Access DB, and you will need to have created the table on the SQL BE DB prior to linking to it. Once that is all configured, try this syntax:

Insert all values of a table into another table in SQL
http://stackoverflow.com/questions/576441/insert-all-values-of-a-table-into-another-table-in-sql

Failing this, I know of no other reliable option. Hopefully JET / SQL do not fight over the SQL dialect differences as not using a Pass-Through query, you will be at the mercy of JET / SQL getting along together.

 

[mahie]

Hi,
tried creating a SQL Pass-through query.. However it asks me for a DSN while executing it..
On providing the DSN I get an error "Invalid Object Name: " <name of my local Access table>

Not sure if what I did is correct.. :banghead:

Regards,
Mahie

 

[mdlueck]

tried creating a SQL Pass-through query.. However it asks me for a DSN while executing it..
On providing the DSN I get an error "Invalid Object Name: " <name of my local Access table>

Not sure if what I did is correct.. :banghead:

Correct based on what? Certainly not my latest suggestion in #4 at least.

Perhaps using a non Pass-Through query to SELECT from the Access table and INSERT into the SQL BE DB table. You will need a Linked Table object in the Access DB, and you will need to have created the table on the SQL BE DB prior to linking to it. Once that is all configured, try this syntax:

Insert all values of a table into another table in SQL
http://stackoverflow.com/questions/576441/insert-all-values-of-a-table-into-another-table-in-sql

Queries in Pass-Through mode CANNOT reference any local objects in the FE DB as the query was Passed-Through to the server... thus the name Pass-Through query.