Posts tagged ODBC

Linked Table Query Returning Incorrect Results

Aug18
2010 Leave a Comment Written by admin

We had this annoying problem: a simple query in Access to a linked table (using ODBC and MS SQL server) returned incorrect results, though the same query returned correct results in MS SQL server. By incorrect I mean the field values were different, so rather than returning AZ, the query returned BZ (some other value in that field).

We thought that it was a table size issue, may be the driver could not handle huge data. However, the problem was with the “unique record identifier” while linking the table, as JM reported here. I did not have this problem any longer when I did not select any fields for the unique record identifier while linking the table.

Posted in Access - Tagged , , ,

Access VBA: Export Access tables using ODBC

Jul18
2007 3 Comments Written by admin

If you want to export Access tables using ODBC/DSN connections, use the following code. This procedure uses the File DSN and ODBC connection to export Access tables using DAO object TableDef.

Sub ExportTbls()

Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef

On Error GoTo ExportTbls_Error

sTypExprt = “ODBC Database” ‘Export Type
sCnxnStr = “ODBC;DSN=DSNName;UID=userID;PWD=password” ‘Create the connection string

vStTime = Timer
Application.Echo False, “Visual Basic code is executing.”

Set db = CurrentDb()

‘need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
‘Don’t export System and temporary tables
If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 4) “~TMP” Then
‘Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef

MsgBox “Done! Time taken=” & Timer – vStTime

On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub

ExportTbls_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ExportTblsODST”
Resume SmoothExit_ExportTbls
End Sub

Posted in Access, Useful Procedures - Tagged ,