Åbne forbindelse far Access til SQL Server

heres a link to an example which should help you
http://www.dreamincode.net/forums/showtopic24677.htm

I have tried it and now it tells me that the server does not exist or access not allowed (another error than before). I have contacted our IT department as I can make connection with a Visual Studio project with the same information. I will let you know.

have you tried linking the tables using an ODBC data source? Or an Access data project (ADP)

I do this:

Public Conn As New ADODB.Connection
Conn.Open _
"Provider = sqloledb;" & _
"Data Source={SQLServername};" & _
"Initial Catalog={DBname};" & _
"User ID={user};" & _
"Password={pw};"

You could try one of the other methods to see if you can connect.

Brug nedenstående function, således:

Call AttachDSNLessTable("TabelNavnAccess", "dbo.TabelnavnSQLserver", "Servernavn", "DatabasenavnPåServer", "BrugerNavn", "Password")


Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Denne virker:

Set Conn = CreateObject("ADODB.Connection")
Conn.Open _
"Provider=sqloledb;" & _
"Data Source=ServerID;" & _
"Initial Catalog=Databasenavn;" & _
"User Id=BrugerID;" & _
"Password=pw"

thanks