UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Odbc Connection To Ms Access, Access 2010    
 
   
RodFard
post Dec 2 2019, 06:44 PM
Post#1



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Hi;
Before I start, my apologies in advance if this is not the right forum to ask my question;

I connect my ms access tables to our ERP system via ODBC. We are changing our ERP system and therefore, I will need to re-establish my databases. I was told by IT that I have all the required permissions. However, I seem to have 2 different problems.
1) Originally I could see the tables that I need to link to, then I would link without any issues, but the tables would show only a few fields (2 fields, or on another table 4 fields)
2) then I can still see the tables, but when I try to link to any of them, I get an error. I have attached a few snap shots.

I tried a similar thing via Excel, and I had no issues at all in linking the tables to my workbook. This leads me to believe that I don't have any permission issues, and it should be a setting in ms access
I greatly appreciate your help.
Regards


Attached File(s)
Attached File  Slide4.jpg ( 50.79K )Number of downloads: 4
Attached File  Slide3.jpg ( 48.8K )Number of downloads: 1
Attached File  Slide2.jpg ( 86.25K )Number of downloads: 0
Attached File  Slide1.jpg ( 40.13K )Number of downloads: 0
 

--------------------
Regards
Rod
Go to the top of the page
 
theDBguy
post Dec 2 2019, 06:53 PM
Post#2


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi Rod. Not in front of a computer now, but there seems to be a missing step in there. Did it ask you to select a DSN, or did you create a new one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Dec 2 2019, 06:53 PM
Post#3


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Which driver are you choosing?

I have 2 on my box for connecting to SQLServer - one works, the other doesn't, but right now I can't remember which is which, and I'm replying from my phone.

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 2 2019, 10:59 PM
Post#4



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Yes, I set up the connections from Windows ODBC connection. It does work when loading the tables from Excel.

--------------------
Regards
Rod
Go to the top of the page
 
RodFard
post Dec 2 2019, 11:00 PM
Post#5



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


What do you mean driver? How can I find that information?

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 04:48 AM
Post#6


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Paste the following functions into a standard module:
CODE
Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Function  : GetODBCDrivers
' Author    : dm
' Date      : 03/11/2014
' Purpose   : Enumerate ODBC drivers
' Arguments : arrODBCDrivers  - Optional; array to store returned matches
'                               If omitted, results are simply print to Immediate Window
'             strFilter       - Optional; string to be found in driver name
'                               eg "MySQL", "SQL Server", "Excel"
'---------------------------------------------------------------------------------------
'
Public Function GetODBCDrivers(Optional arrODBCDrivers As Variant, _
                               Optional strFilter As String = "SQL") As Boolean
  
  Const ODBC_REG_PATH As String = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

  Dim arrDrivers() As Variant, arrValueTypes() As Variant, _
      i As Integer, vDriver As Variant

  Call EnumerateRegEntries(ODBC_REG_PATH, arrDrivers, arrValueTypes)
  If Not IsEmpty(arrDrivers) Then
    For Each vDriver In arrDrivers
      If (InStr(vDriver, strFilter)) Then
        If Not IsMissing(arrODBCDrivers) Then
          If i = 0 Then
            ReDim arrODBCDrivers(i)
          Else
            ReDim Preserve arrODBCDrivers(i)
          End If
          arrODBCDrivers(i) = vDriver
          i = i + 1
        Else
          Debug.Print vDriver
        End If
      End If
    Next
    GetODBCDrivers = (Err = 0)
  End If

End Function

Public Function EnumerateRegEntries(strKeyPath As String, arrEntryNames As Variant, arrValueTypes As Variant) As Boolean
    
  Const HKEY_CLASSES_ROOT = &H80000000
  Const HKEY_CURRENT_USER = &H80000001
  Const HKEY_LOCAL_MACHINE = &H80000002
  Const HKEY_USERS = &H80000003
  Const HKEY_CURRENT_CONFIG = &H80000005
  Const WINMGMTS_REG As String = "winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv"

  Dim objReg As Object
  
  Set objReg = GetObject(WINMGMTS_REG)
  objReg.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrEntryNames, arrValueTypes

End Function


Then in the Immediate Window (Ctrl+G) type the following and hit enter:
CODE
?GetODBCDrivers

Post the output back here.

When I do I get:
CODE
?GetODBCDrivers
SQL Server
SQL Server Native Client 11.0
ODBC Driver 13 for SQL Server
MySQL ODBC 5.3 ANSI Driver
MySQL ODBC 5.3 Unicode Driver
True

IIRC, I can't read the list of tables when using either of the {SQL Server} or {SQL Server Native Client 11.0} drivers

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 3 2019, 10:00 AM
Post#7



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Hi;
I get the following in both ms access and Excel:

?GetODBCDrivers
SQL Server
True

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 3 2019, 11:23 AM
Post#8


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Yes, I just tested on a work machine.

Using that driver ( { SQL Server } ), I can only see system tables when creating a file DSN

I think the other drivers I have listed ( { SQL Server Native Client 11.0 }, { ODBC Driver 13 for SQL Server }) got installed on this machine when installing SSMS, but I'm sure you must be able to download them separately.

Others here will have a better idea.

shrug.gif

d

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 4 2019, 10:54 AM
Post#9



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Hi;
I downloaded the latest update to the ODBC driver. It is still showing a limited number of fields. Any ideas as to why the power query in excel shows everything?
Thanks

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 12:03 PM
Post#10


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Are there any unusual datatypes in the fields?

Do all tables have primary keys?

Is there a timeout issue - you have an image of a network interruption.

What do you see if you check the table via code.
CODE
Function TableDetails(tbl As String) As Boolean

  Dim db As DAO.Database, strDataType As String, i As Integer
  
  Set db = CurrentDb
  With db.TableDefs(tbl)
    Debug.Print "Table:", tbl
    Debug.Print "cn string:", .Connect
    For i = 0 To .Fields.Count - 1
      Select Case .Fields(i).Type
        Case dbBoolean: strDataType = "BIT"
        Case dbByte: strDataType = "BYTE"
        Case dbCurrency: strDataType = "MONEY"
        Case dbDate: strDataType = "DATETIME"
        Case dbDouble: strDataType = "DOUBLE"
        Case dbInteger: strDataType = "INTEGER"
        Case dbLong  'Long
          'test if counter, doesn't detect random property if set
          If (.Fields(i).Attributes And dbAutoIncrField) Then
            strDataType = "COUNTER"
          Else
            strDataType = "LONG"
          End If
        Case dbDecimal: strDataType = "DECIMAL"
        Case dbFloat: strDataType = "FLOAT"
        Case dbMemo: strDataType = "MEMO"
        Case dbSingle: strDataType = "SINGLE"
        Case dbText: strDataType = "VARCHAR(" & .Fields(i).Size & ")"
        Case dbGUID: strDataType = "GUID"
        Case dbLongBinary: strDataType = "LONGBINARY"
        Case Else: strDataType = "Other"
      End Select
    
      Debug.Print .Fields(i).Name, strDataType
    Next i
  End With
  Set db = Nothing
  TableDetails = (Err = 0)
  
End Function


In the IMmediate Window (Ctrl+G):
CODE
?TableDetails("NameOfLinkedTable")

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 4 2019, 02:07 PM
Post#11



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Thanks David for your response.

I get an error on the following line:
With db.TableDefs(tbl)

I attached a snap shot of the references, in case I am missing anything.

I don't get any timeout errors. I am assuming the tables would have primary keys as the ERP system was purchased from a major vendor. I do get only some of the fields. for example there is one table with 50 fields, I get only 2. using power query in excel on the same machine produces all the fields.

Regards
Attached File(s)
Attached File  Slide5.jpg ( 81.52K )Number of downloads: 0
 

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 02:14 PM
Post#12


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Just checking, Rod, you did change "NameOfLinkedTable" to the actual name of your linked table (I'd guess it's 'dbo_something')?

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 4 2019, 02:35 PM
Post#13



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


lol. no I didn't. I did now.

Here is what I get from one table. This table has over 200 fields, but I get less than 30.

Table: dbo_item_mst
cn string: ODBC;DSN=INFOR-SQL-D-INFORSQL;Description=INFOR-SQL-D-INFORSQL;UID=INFORReady;PWD=Password1;APP=Microsoft Office;DATABASE=SyteLine_App_UAT
lowdate DATETIME
rcpt_rqmt VARCHAR(1)
Uf_Items_ReviewedBy VARCHAR(30)
Uf_Items_ReviewedDate DATETIME
Uf_PLM_ConfigurationDescription MEMO
Uf_PLM_CrossSectionShape MEMO
Uf_PLM_CVNTemp MEMO
Uf_PLM_Dimension1 DECIMAL
Uf_PLM_Dimension2 DECIMAL
Uf_PLM_Dimension3 DECIMAL
Uf_PLM_Dimension4 DECIMAL
Uf_PLM_MaterialClass VARCHAR(32)
Uf_PLM_MTRClass MEMO
Uf_PLM_NonProcurable VARCHAR(1)
Uf_PLM_PartGroup MEMO
Uf_PLM_Phase MEMO
Uf_PLM_ProductCode1 VARCHAR(40)
Uf_PLM_ProductCode1Description VARCHAR(64)
Uf_PLM_ProductCode3 MEMO
Uf_PLM_ProductCode3Description VARCHAR(64)
Uf_PLM_ProductCode4 VARCHAR(48)
Uf_PLM_ProductCode4Description VARCHAR(64)
Uf_PLM_RMForm MEMO
Uf_PLM_SacrificialLength DECIMAL
Uf_PLM_SeeEngineering MEMO
Uf_PLM_State VARCHAR(32)
Uf_PLM_Type MEMO
True
This post has been edited by RodFard: Dec 4 2019, 02:38 PM

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 02:48 PM
Post#14


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Well, you don't get 50 fields, but there's more than 2!

Is INFOR-SQL-D-INFORSQL a file DSN or a machine DSN?

If it's a file DSN, can you open the file in a text editor and report what it says?

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Dec 4 2019, 02:50 PM
Post#15


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Also, what do you get now if you run
CODE
?GetODBCDrivers
in the Immediate Window?

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 4 2019, 05:25 PM
Post#16



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


here is the ODBC drivers:

?GetODBCDrivers
SQL Server
ODBC Driver 17 for SQL Server
True

The DSN is under machine data source.

--------------------
Regards
Rod
Go to the top of the page
 
RodFard
post Dec 5 2019, 10:20 AM
Post#17



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Hi;
I just ran a pass through query in access and it shows all the columns. Why would the fields show up in the pass through query and not the linked tables?

This leads me to believe that really permission is not the problem.

Any ideas?

Thanks

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 5 2019, 12:42 PM
Post#18


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Let's see if you get the same result using a DSN-less connection:
CODE
Function SQLServerTest(tbl As String) As Boolean

  Dim pt as DAO.QueryDef, strCn As String, strSQL As String, _
      strDataType As String, i As Integer

  Const SERVER  As String = "EnterYourServerAddressOrNameHere", _
        USER    As String = "INFORReady", _
        PW      As String = "EnterYourPasswordHere", _
        DB      As String = "SyteLine_App_UAT", _
        DRIVER  As String = "{ODBC Driver 17 for SQL Server}"

  strCn = "ODBC;DRIVER=" & DRIVER & ";SERVER=" & SERVER & ";DATABASE=" & DB & ";UID=" & USER & ";PWD" & PW
  strSQL = "SELECT * FROM " & tbl & " WHERE 1=0;"

  Set pt = CurrentDb.QueryDefs(vbNullString)
  With pt
    .Connect = strCn
    .SQL = strSQL
    .ReturnsRecords = True
    .ODBCTimeout = 500
    With .OpenRecordset()
      Debug.Print tbl
      Debug.Print strCn
      For i = 0 To .Fields.Count - 1
        Select Case .Fields(i).Type
          Case dbBoolean: strDataType = "BIT"
          Case dbByte: strDataType = "BYTE"
          Case dbCurrency: strDataType = "MONEY"
          Case dbDate: strDataType = "DATETIME"
          Case dbDouble: strDataType = "DOUBLE"
          Case dbInteger: strDataType = "INTEGER"
          Case dbLong  'Long
            'test if counter, doesn't detect random property if set
            If (.Fields(i).Attributes And dbAutoIncrField) Then
              strDataType = "COUNTER"
            Else
              strDataType = "LONG"
            End If
          Case dbDecimal: strDataType = "DECIMAL"
          Case dbFloat: strDataType = "FLOAT"
          Case dbMemo: strDataType = "MEMO"
          Case dbSingle: strDataType = "SINGLE"
          Case dbText: strDataType = "VARCHAR(" & .Fields(i).Size & ")"
          Case dbGUID: strDataType = "GUID"
          Case dbLongBinary: strDataType = "LONGBINARY"
          Case Else: strDataType = "Other"
        End Select
        Debug.Print .Fields(i).Name, strDataType
      Next i
      .Close
    End With
  End With
  Set pt = Nothing
  SQLServerTest = (Err = 0)

End Function


Adjust the server name and password as required in the constants declaration and then try in the Immediate Window:
CODE
?SQLServerTest("NameOfTable")

(Note this time you want the actual table name in SQL server, ie without 'dbo_')

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


Regards,

David Marten
Go to the top of the page
 
RodFard
post Dec 5 2019, 02:39 PM
Post#19



Posts: 160
Joined: 27-October 04
From: Toronto, Ca


Hi;
We resolved the problem. It was permission after all. Our IT didn't know that the new system needs to assign permission at the fields level as well as the tables.
Thanks everyone.
Regards

--------------------
Regards
Rod
Go to the top of the page
 
cheekybuddha
post Dec 5 2019, 02:43 PM
Post#20


UtterAccess Moderator
Posts: 11,907
Joined: 6-December 03
From: Telegraph Hill


Ah good! Glad you got it sorted!

(And apologies, I didn't see your Post#17)

thumbup.gif

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


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:12 AM