Use SQL to Import Data Directly From Sybase Into Access Table

[Access56]

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

I have this code working. But I can only get the query to load the data into a listbox. Does anyone know how I can get it directly into a table(tblTest)? I have tried putting "Inset into tblTest *" at the beginning of the SQL string. It does not cause an error but it does not load the data into the table.

Thank you very much.

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


Code: ( vb )
Private Sub buttonLoad3_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("qryTest")

strSelect = "SELECT state_code, xxx, year, qtr "
strFrom = "FROM production_db.dbo.sy_test sy_test "
strWhere = "WHERE (state_code='88') AND"
strWhere = strWhere & " (xxx='1234567890') AND "
strWhere = strWhere & " (year=2007) AND "
strWhere = strWhere & " (qtr=2)"

sSQL = strSelect & strFrom & strWhere
qdf.SQL = sSQL

qdf.Connect = "ODBC;DSN=Sybase System 11;UID=YYYY;PWD=password;srvr=production;Database= production_db"
Me.listTest.RowSource = "qryTest"
Me.listTest.Requery

DoCmd.DeleteObject acQuery, "qryTest"

End Sub


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

 

[jrbarnett]

1. Create a linked table to your ODBC database table.

2. Create a query with a copy of your SQL, adding the top line:

"Insert into tblTest (State_code, xxx, year, qtr)"

then see if the query runs.

Note that it needs to have the same structure as your source table with equivalent data types, size and primary key.

John

 

[PHV]

...
DoCmd.RunSQL "INSERT INTO tblTest SELECT * FROM qryTest"
DoCmd.DeleteObject acQuery, "qryTest"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886