Access SQL server

http://maximoaccess.forumeiros.com/t9645-tutorial-conexao-odbc-access-com-sql-server


Nessa tutorial você usa tabela vinculada que um bom método pra quem esta a começar usar access com sql server, eu recomendo o uso de Conexões DSN-Less que é uma forma automática de vincular tabelas sem DSN.
Infelizmente não estou a teclar do meu pc não vou poder postar um exemplo do código que uso mais farei isso mais tarde.

Option Compare Database
'/ / Nome: AttachDSNLessTable
'/ / Objetivo: Criar uma tabela vinculada ao SQL Server sem usar um DSN
'/ / Parâmetros
'/ StLocalTableName /: Nome da tabela que você está criando no banco de dados atual
'/ StRemoteTableName /: Nome da tabela que você está ligando para o banco de dados SQL Server
'/ StServer /: Nome do SQL Server que você está ligando
'/ StDatabase /: Nome do banco de dados SQL Server que você está ligando
'/ / StUsername: Nome do usuário do SQL Server que pode se conectar ao SQL Server, deixe em branco para usar uma conexão confiável
'/ / StPassword: senha do usuário SQL Servidor

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
' deletar as tabelas local ao sair do sistema
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUserName) = 0 Then
'/ / Use autenticação confiável se stUsername não é fornecido.
stConnect = "ODBC; DRIVER ={SQL Native Client};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'/ / AVISO: Isto irá poupar o nome de usuário e a senha com as informações da tabela vinculada.
stConnect = "ODBC;DRIVER={SQL Native Client};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword & ";Option=3;"
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encontrou um erro inesperado: " & Err.Description

End Function

Function CreateConnection()
Dim strPasswd As String
Dim strServer As String
Dim strUser As String
Dim strDB As String
Dim strPort As String
strPasswd = "007"' Senha do servidor
strUser = "sa"' Usuario do servidor
strServer = "127.32.9.11\SQLEXPRESS" 'IP do servidor
strDB = "teste"'Banco de Dado
strPort = "1433"'Porta do servidor
Rem error
On Error Resume Next 'Tratamento de erro

'Essa tabela devem ser ligados

'A primeira tabela venda é aqui vai ser criada no access a outra dbo. venda é aqui esta no servidor
Set dummy = AttachDSNLessTable("Venda", "dbo.Venda", strServer, strDB, strUser, strPasswd)

End Function
Obs: Depois de criar o modulo e adicionar esse codigo para vincular as tabelas ao iniciar o access, deve criar um macro autoexec e adicionar o codigo =CreateConnection()na acção ExecutarCódigo.
Esse é a forma que eu uso para trabalhar com servidores SQL Server, MySQL ou Oracle funciona normalmente com essa forma também podem trabalhar com formularios desvinculado as tabelas.

Muito bom avelino007, obrigado por compartilhar o conhecimento.

Sem makas mano estamos aqui para partilhar conhecimento e acabar com o velho mito que dizem que o ms access não vale nada, ainda estou a espera do teu proximo tutorial sobre conectar o ms access com SQL Server em rede gostaria de saber porque que voce diz que para conectar o ms access em rede tem que ter instalado o ms access no servidor, sera? que voce pertende usar um arquivo ADP para poder conectar SQL Server em rede. Ola o codigo que eu postei também vincula as tabelas do Servidor em rede ele ao carrengar demora uns segundos ao vincular as tabelas,tu pode usar um formalario para mostrar que estas carrengar o sistema, depois voce trabalha sem complicação nenhuma; qual a vantagem desse codigo quando voce quiser vender o teu sistema só precisa configurar o servidor do teu cliente com toda informação de acesso ao servidor para vincular as tabelas e ainda a senha do servidor não é armazenada no ms access.
Obs: Brevemente vou postar um exemplo que estou a tentar desenvolver que é uma maneira de não vincular a tabela login no ms access e conectar direitamente no servidor se tens um exemplo desse pode mim dar uma ajuda estou a precisar muito disso, por enquanto vou usar o OPEN do mestre Avelino.

Bom dando continuidade ao tutorial do nosso amigo gilberlanio, estou a postar um exemplo de como vincular tabela de um servidor sem DNS ao Ms Access, nesse exemplo uso um formulario desvinculado do mestre JPAULO para acessar tabela no access.
Obs.: Se ainda não tenhem um servidor instalado basta baixar e exportar a tabela para o servidor e fazer alteração no modulo AttachDSNLessTable e definir a forma de acesso ao teu servidor.

Bom pessoal por favor quem já testou o codigo e encontrou algo erro dei o retorno.

Boa tarde.

eu tenho duas dúvidas, (por enquanto kkk):

qd eu fecho a aplicação ele nao exclui as tabelas "vinculadas", gostaria de lembrar que tenho alem destas, mais duas que fica dentro do aplicativo, não vinculadas, utilizo elas como tabelas de parametros.

a outra duvida é: como faço para pegar todas as tabelas de uma só vez? vou ter que colocar uma linha para cda ou tem uma forma mais simples?


obrigado desde ja.

ton0612 boa tarde nada verdade o access elimina a conexao se você fazer o teste ao carregar o sistema apertar a tecla Shift e tentar abrir a tabela vai notar que ele não estará vinculada, não é possivel vincular todas tabelas tem que ser linha a linha, imagina que ao tentar vincular todas tabelas do sql server também acabas vinculado as tabelas do banco de dado sistema.


Não esquece que para vincular as tabelas adiciona também a porta do servidor.

veja

Código:

'/ / Nome: AttachDSNLessTable
'/ / Objetivo: Criar uma tabela vinculada ao SQL Server sem usar um DSN
'/ / Parâmetros
'/ StLocalTableName /: Nome da tabela que você está criando no banco de dados atual
'/ StRemoteTableName /: Nome da tabela que você está ligando para o banco de dados SQL Server
'/ StServer /: Nome do SQL Server que você está ligando
'/ StDatabase /: Nome do banco de dados SQL Server que você está ligando
'/ / StUsername: Nome do usuário do SQL Server que pode se conectar ao SQL Server, deixe em branco para usar uma conexão confiável
'/ / StPassword: senha do usuário SQL Servidor
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUserName As String, Optional stPassword As String, Optional strPort 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 autenticação confiável se stUsername não é fornecido.
             stConnect = "ODBC;DRIVER={SQL Server Native Client 10.0}r;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;"
    Else
        '/ / AVISO: Isto irá poupar o nome de usuário ea senha com as informações da tabela vinculada.
      
       stConnect = "ODBC;DRIVER={SQL Server Native Client 10.0};SERVER=" & stServer & ";UID=" & stUserName & ";PWD=" & stPassword & ";DATABASE=" & stDatabase & ";Port=" & strPort
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encontrou um erro inesperado: " & Err.Description
    

End Function
Function CreateConnection()
  Dim strPasswd As String
    Dim strServer As String
    Dim strUser As String
    Dim strDB As String
    Dim strPort As String
    
   strPasswd = "0000"
    strUser = "sa"
    strServer = "HALLOWEB\SQLEXPRESS"
    strDB = "venda"
    strPort = "1433"
      'Rem error
    On Error Resume Next    'Tratamento de erro

    '"Essas tabelas devem ser ligados


 Set dummy = AttachDSNLessTable("Venda", "Venda", strServer, strDB, strUser, strPasswd, strPort)
 Set dummy = AttachDSNLessTable("Cliente", "Cliente", strServer, strDB, strUser, strPasswd, strPort)
 Set dummy = AttachDSNLessTable("DetalheVenda", "DetalheVenda", strServer, strDB, strUser, strPasswd, strPort)
 Set dummy = AttachDSNLessTable("Produto", "Produto", strServer, strDB, strUser, strPasswd, strPort)
    

 End Function

Obrigado pelo retorno.

Quanto a parte de abrir as tabelas sem problemas, vou fazer uma a uma.
Eu também poderia "abrir" as tabelas de acordo com o uso, certo? ou isso acarretária em muito uso do servidor?

O que não entendi muito bem foi o caso de não apagar as tabelas, ela ainda continua lá mesmo apertando a tecla shift.

Tem algum procedimento que tenho que fazer antes de tudo. eu iniciei um projeto novo e só coloquei um ou dois forms, para testar.
o motivo de eu testar é porque tenho um projeto bem grandinho para modificar e precisarei fazer uma conexão ao banco pela internet e tambem local. Mas tive dificuldade com o que fiz neste link, http://maximoaccess.forumeiros.com/t17374-resolvidorowsource-em-caixa-de-combinacao, mas um exemplo seu que adaptei (obrigado).


Obrigado novamente.

Boa tarde Avelino e demais membros.
Bom fiz um estudo dentro do seu código e mais alguns outros e montei um módulo. Sendo que neste modificação ele ja verifica todas as tabelas presentes no SQL server e inclui o vinculo no banco de dados atual.

Código:

Global conSql As New ADODB.Connection
Global rs1 As New ADODB.Recordset
Global strConect As String
Global strSelect As String


'/ / Nome: AttachDSNLessTable
'/ / Objetivo: Criar uma tabela vinculada ao SQL Server sem usar um DSN
'/ / Parâmetros
'/ StLocalTableName /: Nome da tabela que você está criando no banco de dados atual
'/ StRemoteTableName /: Nome da tabela que você está ligando para o banco de dados SQL Server
'/ StServer /: Nome do SQL Server que você está ligando
'/ StDatabase /: Nome do banco de dados SQL Server que você está ligando
'/ / StUsername: Nome do usuário do SQL Server que pode se conectar ao SQL Server, deixe em branco para usar uma conexão confiável
'/ / StPassword: senha do usuário SQL Servidor

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUserName As String, Optional stPassword As String, Optional strPorta 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
' deletar as tabelas local ao sair do sistema
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUserName) = 0 Then
'/ / Use autenticação confiável se stUsername não é fornecido.
stConnect = "ODBC; DRIVER ={SQL Native Client};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'/ / AVISO: Isto irá poupar o nome de usuário e a senha com as informações da tabela vinculada.
 stConnect = "ODBC;DRIVER={SQL Server Native Client 10.0};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword & ";Option=3;"
End If

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encontrou um erro inesperado: " & Err.Description

End Function

Function CreateConnection()
Dim strPasswd As String
Dim strServer As String
Dim strUser As String
Dim strDB As String
Dim strPort As String

Dim tblTRemoto As String
Dim tblTLocal As String

strPasswd = "artesita" ' Senha do servidor
strUser = "pcp" ' Usuario do servidor
strServer = "TON-PC\PCPEXPRESS" 'IP do servidor
strDB = "PCP2" 'Banco de Dado
strPort = "1433" 'Porta do servidor
'Rem error
On Error Resume Next 'Tratamento de erro

'Conexão com servidor de acordo com o estabelido acima.
strConect = "Provider=SQLNCLI10;Server=" & strServer & "," & strPort & ";Database=" & strDB & ";Uid=" & strUser & ";Pwd=" & strPasswd & ";"
'strConect = "ODBC;DRIVER={SQL Server Native Client 10.0};SERVER=" & strServer & ";DATABASE=" & strDB & ";UID=" & strUser & ";PWD=" & strPasswd & ";Option=3;"
conSql.Open strConect
strSelect = "Select * From sys.tables"
rs1.Open strSelect, conSql, adOpenDynamic, adLockPessimistic

'Rotina que adiciona o nome da tabela.
Do While Not rs1.EOF
'Aqui defini o nome da tabela local igual a tabela remota.
tblTRemoto = rs1.Fields(0).Value

'Essa tabela devem ser ligados
'A primeira tabela "tblTeste" é a qual será criada no access a outra "dbo.tblTeste" é a que esta no servidor.
Set dummy = AttachDSNLessTable(tblTRemoto, tblTRemoto, strServer, strDB, strUser, strPasswd, strPort)
rs1.MoveNext
Loop

rs1.Close
Set rs1 = Nothing
conSql.Close
Set conSql = Nothing

End Function
'//Obs: Depois de criar o modulo e adicionar esse codigo para vincular as tabelas ao iniciar o access,
'//deve criar um macro autoexec e adicionar o codigo =CreateConnection()na acção ExecutarCódigo.
'//Esse é a forma que eu uso para trabalhar com servidores SQL Server,
'//MySQL ou Oracle funciona normalmente com essa forma também podem trabalhar com formularios desvinculado as tabelas.

Public Function DesligaVinculo()
Dim dbs As Database
Dim tbls As String
Dim stSel As String
Dim rst As Recordset

'localiza as tabelas vinculadas
stSel = "select * from MSysObjects where not isnull (Connect)"
'Seleciona o banco de dados Local
Set dbs = CurrentDb
'Abre registro de tabelas vinculadas
Set rst = dbs.OpenRecordset(stSel)

'Inicia a rotina para excluir as tabelas vinculadas
Do While Not rst.EOF
'define o nome da tabela no registro
tbls = "Drop Table [" & rst("Name") & "]"
'Exclui a tabela
dbs.Execute tbls
'vai para o próximo registro
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


Caso tenha alguma sugestão ou crítica, estou a disposição.

ton0612 bom dia ótima alteração feita, para quem esta pensar em vincular as tabelas do servidor ao Access é uma boa alternativa, mais atenção esse método pode Sub-carregar o servidor, actualmente estou pouco parado no desenvolvimento dos meus projectos devido ao TCC da faculdade ultimo ano, mais quando comecei a usar o Mysql e Access essa foi o primeiro método que usei agora usa mais Stored Procedura do Mysql para trabalhar com MS ACCESS é a melhor forma de se trabalhar desvinculado na minha opinião.

Viva Avelino
Mas este código cria tabelas locais no access iguais às que estão no servidor sql server? se sim, como você faz para atualizar a informação quando altera algum dos dados, dado que a funcionar em rede á atualizações constantes.
Um Abraço
Rui

Boa Tarde Rui Sergio, o que acontece com esse codigo, ele vincula as tabelas do SQL Server para Access, funciona do mesma forma semelhante ao um Banco de Dado Access e seu Forte-end.

Ok, pensei que importasse para uma tabela local, eu utilizo formulários sem tabelas vinculadas ligando ao sql server, pretendia fazer o mesmo para relatorios continuos mas dá erro.
Obrigado pelo esclarecimento Avelino

Rui na minha opinião tens duas forma ou vincula a tabela do SQL Server no Access e ao invés vincular essa tabela ao relatório cria uma tabela temporária para carregar dados para o relatório ou usa tabela temporária ir buscar dados no SQL Server remotamente sem vincular tabela nenhuma.

Avelino, na sua opinião qual é a forma mais rápida?
Como você faz para criar uma tabela temporária local?
Abraço

Bom Dia Eu criou tabela temporária ao reiniciar o sistema, estou sem o meu computador pessoal aqui no serviço, veja o exemplo de criar tabela temporária no site do Avelino Sampaio.

Viva
Procurei em seu site mas não estou encontrando, poderia mandar o link para mim?

Rui Sergio veja no meu perfil tem exemplo para esse efeito

Veja

Obrigado pelo exemplo Avelino
Um Abraço

Viva
Tenho tabelas vinculadas, umas ao sql server outras a ficheiros excel e outras locais.
Tem como atualizar o vinculo das tabelas vinculadas ao sql server de acordo com o user que faz login?
(O user que faz login existe como user no sql server)
A ideia é a tabela vinculada só disponibilizar os registos associados a cada utilizador.

Viva
Resolvi desta forma, mas para o novo link ficar ativo tenho de sair e voltar a entrar no access, tem como resolver?

Dim tdfTable As TableDef
Dim strConnect As String
strConnect = "ODBC;DRIVER=SQL Server;SERVER=XXXX;UID=XXXX;PWD=XXXX;DATABASE=XXXX"

For Each tdfTable In CurrentDb.TableDefs
       If Len(tdfTable.Connect) > 0 Then
       tdfTable.Connect = strConnect
       tdfTable.RefreshLink
       End If
Next
CurrentDb.TableDefs.Refresh

Rui Sérgio bom dia, não estou atender a tua duvida

Bom dia Avelino
Acontece o seguinte:
Ao abrir o access a tabela esta ligada com o user X, e ao abrir a tabela mostra os registos associados X.
Ao ligar as tabelas com o codigo referido para o user Y, ao abrir a tabela continua a mostrar os registos associados ao user X.
Saio do access e volto a entrar, ao abrir a tabela já mostra os registos associados ao user Y.

A ideia é que quando eu aplique o código ao abrir a tabela me mostre logo os registos associados a esse user.
A tabela só assume o novo link depois de sair e voltar a entrar, não sei porquê.
Espero ter-me feito entender.

tenta fazer com que antes de abrir a tabela usa um codigo, antes abrir tabela já uma actualização da tabela invisível.

Avelino
Fiz o que você disse e não funcionou, também executei o comando compactar e reparar e também não funcionou.
O comando tdfTable.RefreshLink não deveria atualizar o link?

Desta forma que você quer terás muito problema no desempenho do sistema, porque não usa tabela temporária ao iniciar o sistema só carrega os dados do usuário que fez o login.

Obrigado pela dica Avelino, mas no meu caso como os dados estão sempre a ser atualizados a tabela temporária fica mais lento

Depende como você vai usar a tabela temporária, gosto de usar para alimentar os relatórios não preciso carregar todo os dados,basta você saber usar filtro, agora gravar dados,editar e excluir faço directamente no servidor.

Código:

Function importar_Produto()
Dim Db2 As Database
Dim Rs2 As Recordset
On Error Resume Next
dataset.Close 'fecha o recorset
banco.Close 'fecha o banco de dados
Set dataset = Nothing
Rs2.Close
Set Db2 = Nothing
Set Db2 = CurrentDb
On Error GoTo trata

Comando = "Select codProduto,descricao,unidade,valorUnitario,estoqueMinimo,qtdEstoque,estado,iva,tabelaiva  from produto WHERE codProduto=" & Nz(Me!txtcod) & " ORDER BY codProduto;"
Call executa

dataset.MoveFirst
Set Rs2 = Db2.OpenRecordset("Produto")
Do While Not dataset.EOF
Rs2.AddNew
If IsNull(dataset!codProduto) = True Or dataset!codProduto = "" Then
Else
Rs2!codProduto = dataset!codProduto
End If
If IsNull(dataset!descricao) = True Or dataset!descricao = "" Then
Else
Rs2!descricao = dataset!descricao
End If
If IsNull(dataset!unidade) = True Or dataset!unidade = "" Then
Else
Rs2!unidade = dataset!unidade
End If
If IsNull(dataset!valorUnitario) = True Or dataset!valorUnitario = "" Then
Else
Rs2!valorUnitario = dataset!valorUnitario
End If
If IsNull(dataset!qtdEstoque) = True Or dataset!qtdEstoque = "" Then
Else
Rs2!qtdEstoque = dataset!qtdEstoque
End If
If IsNull(dataset!estoqueMinimo) = True Or dataset!estoqueMinimo = "" Then
Else
Rs2!estoqueMinimo = dataset!estoqueMinimo
End If
If IsNull(dataset!estado) = True Or dataset!estado = "" Then
Else
Rs2!estado = dataset!estado
End If

If IsNull(Rs2!IVA) = True Or Rs2!IVA = "" Then
Else
rs!IVA = Rs2!IVA
End If
If IsNull(Rs2!tabelaiva) = True Or Rs2!tabelaiva = "" Then
Else
rs!tabelaiva = Rs2!tabelaiva
End If
Rs2.Update
dataset.MoveNext
Loop
dataset.Close
cn.Close
Set dataset = Nothing
Rs2.Close
Set Db2 = Nothing
sai:
Exit Function
trata:
If Err = 3021 Then
Resume Next
Else
Set dataset = Nothing
Resume sai
End If
End Function

Código:

Function Exportar_DetalheVenda()
Dim Db2 As Database
Dim Rs2 As Recordset

On Error Resume Next
rs.Close 'fecha o recorset
cn.Close 'fecha o banco de dados
Set rs = Nothing

Rs2.Close
Set Db2 = Nothing

Set Db2 = CurrentDb
On Error GoTo trata
Call MySQL_Server
cn.Open "Driver={MySQL ODBC 5.5.25a Driver};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & "; Password=" & MyslqSenha & ";Option=3;"
rs.CursorLocation = adUseClient

rs.Open "Select codVenda,codProduto,qtdProduto,iva,tabelaiva " & _
            "From detalhevenda ", cn, adOpenDynamic, adLockOptimistic

           
Set Rs2 = Db2.OpenRecordset("DetalheVenda")

Do While Not Rs2.EOF
rs.AddNew

If IsNull(Rs2!codVenda) = True Or Rs2!codVenda = "" Then
Else
rs!codVenda = Rs2!codVenda
End If

If IsNull(Rs2!codProduto) = True Or Rs2!codProduto = "" Then
Else
rs!codProduto = Rs2!codProduto
End If

If IsNull(Rs2!qtdProduto) = True Or Rs2!qtdProduto = "" Then
Else
rs!qtdProduto = Rs2!qtdProduto
End If
If IsNull(Rs2!IVA) = True Or Rs2!IVA = "" Then
Else
rs!IVA = Rs2!IVA
End If
If IsNull(Rs2!tabelaiva) = True Or Rs2!tabelaiva = "" Then
Else
rs!tabelaiva = Rs2!tabelaiva
End If
rs.Update

Rs2.MoveNext
Loop

rs.Close
cn.Close
Set rs = Nothing

Rs2.Close
Set Db2 = Nothing

sai:
Exit Function

trata:

If Err = 3021 Then
Resume Next

Else
Set rs = Nothing

Resume sai
End If
End Function



Código:

Public Sub executa()
  'inicializa o dataset, executa o comando sql passado através da variavél Comando e preenche o dataset na memória
  If banco.State = 1 Then    'verificar o status do banco de dados. Se aberto fecha a conexão
        banco.Close
    End If

    If dataset.State = 1 Then    'verificar o status do recordeset. Se aberto fecha a conexão
        dataset.Close
    End If
    Call MySQL_Server    'Carrega parametros do servidor
    banco.Open "Driver={MySQL ODBC 5.5.25a Driver};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & "; Password=" & MyslqSenha & "; Port=" & MyslqPorta ''& ";Option=3;"
  dataset.CursorLocation = adUseClient
  dataset.Open Comando, banco, adOpenDynamic, adLockOptimistic
End Sub
'---------------------------------------------------------------------------------------
'  PROCEDIMENTO: MySQL_Server
'    DESCRIÇÃO: Carrega os dados para conexão ao servidor MySQL
'---------------------------------------------------------------------------------------
Public Sub MySQL_Server()

    If sErr = -1 Then    'Habilita tratamento de erro
        On Error GoTo MySQL_Server_Erro
    End If
    MyslqServidor = DLookup("[Servidor]", "Servidor", "[ID]=18")    'Servidor Web
    MyslqUsuario = DLookup("[USServer]", "Servidor", "[ID]=18")    'Usuário do banco de dados
    MyslqSenha = DLookup("[PWServer]", "Servidor", "[ID]=18")    'Senha do banco de dados
    MyslqDatabase = DLookup("[DbServer]", "Servidor", "[ID]=18")    'Database
    MyslqPorta = DLookup("[Port]", "Servidor", "[ID]=18")    'Porta source
    Source = DLookup("[source]", "Servidor", "[ID]=18")    'fonte

    On Error GoTo 0
    Exit Sub

MySQL_Server_Erro:
    DoCmd.Hourglass False
    MsgBox "Ocorreu um erro na aplicação." & vbCr & "Relate os dados abaixo ao suporte." & vbCr & _
          "Descrição do erro: " & Err.Description & vbCr & _
          "Módulo: " & "Parametros_MySQL_Conexao" & vbCr & _
          "Procedimento: " & "MySQL_Server" & vbCr & _
          "Linha: " & Erl, vbExclamation

End Sub

'---------------------------------------------------------------------------------------
'  PROCEDIMENTO: Conexao_Open
'    DESCRIÇÃO: Realiza a conexão com o servidor MySQL
'---------------------------------------------------------------------------------------
Public Function Conexao_Open(csql)

    If sErr = -1 Then    'Habilita tratamento de erro
        On Error GoTo Conexao_Open_Erro
    End If

 Call MySQL_Server    'Carrega parametros do servidor
    If cn.State = 1 Then    'verificar o status do banco de dados. Se aberto fecha a conexão
        cn.Close
    End If

    If rs.State = 1 Then    'verificar o status do recordeset. Se aberto fecha a conexão
    rs.Close
    End If

          Select Case Source
           
            Case "PostgreSQL"
        ' Call MySQL_Server    'Carrega parametros do servidor
        cn.Open "Driver={PostgreSQL ANSI};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & "; Password=" & MyslqSenha & "; Port=" & MyslqPorta & ";XaOpt=1;"
        rs.CursorLocation = adUseClient
        rs.Open csql, cn, adOpenDynamic, adLockOptimistic
       
            Case "MySQL"
        'Call MySQL_Server    'Carrega parametros do servidor
        cn.Open "Driver={MySQL ODBC 5.5.25a Driver};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & "; Password=" & MyslqSenha & "; Port=" & MyslqPorta & ";Option=3;"
        rs.CursorLocation = adUseClient
        rs.Open csql, cn, adOpenDynamic, adLockOptimistic
       
           
            Case "Access"
            cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & location
             
       
        Case "SQLite"
          'Call MySQL_Server    'Carrega parametros do servidor
        cn.Open "Driver={SQLite3 ODBC Driver};Server=" & MyslqServidor & ";Database=" & MyslqDatabase & ";User=" & MyslqUsuario & "; Password=" & MyslqSenha & "; Port=" & MyslqPorta
        rs.CursorLocation = adUseClient
        rs.Open csql, cn, adOpenDynamic, adLockOptimistic
   
On Error GoTo 0
Exit Function

Conexao_Open_Erro:

 DoCmd.Hourglass False
    MsgBox "Ocorreu um erro na aplicação." & vbCr & "Relate os dados abaixo ao suporte." & vbCr & _
          "Erro Nº: " & Err.Number & vbCr & _
          "Descrição do erro: " & Err.Description & vbCr & _
          "Módulo: " & "Parametros_MySQL_Conexao" & vbCr & _
          "Procedimento: " & "Conexao_Open" & vbCr & _
          "Linha: " & Erl, vbExclamation
       
End Select


End Function

Também podes importar ou exportar só os dados que estas a trabalhar basta usar o filtro  WHERE, é uma pena que estou sem tempo para programar estou a fazer um mestrado em administração de empresa, tive que pára uma técnica que estava a desenvolver de trabalhar com Access e Mysql totalmente desvinculado usado Stored Procedure, tabela temporaria, Listview e Treeview.

Obrigado pelas dicas e exemplos Avelino
Já descobri o erro, tinha tabelas ODBC ligadas com um user a que tos tinham acesso aos dados, e outras tabelas ODBC que limitava os registos conforme o User, nestas situações o access entra em conflito, só permite ter tabelas ODBC ligadas de um unico User

Bom dia Avelino!

Estou tentando usar esse exemplo e o mesmo está apontando o seguinte erro:

AttachDSNLessTable encontrou um erro inesperado: ODBC -- Falha na chamada.

Estou usando o SQL Server 2008.

Você poderia me ajudar?

Código:



'/ / Nome: AttachDSNLessTable
'/ / Objetivo: Criar uma tabela vinculada ao SQL Server sem usar um DSN
'/ / Parâmetros
'/ StLocalTableName /: Nome da tabela que você está criando no banco de dados atual
'/ StRemoteTableName /: Nome da tabela que você está ligando para o banco de dados SQL Server
'/ StServer /: Nome do SQL Server que você está ligando
'/ StDatabase /: Nome do banco de dados SQL Server que você está ligando
'/ / StUsername: Nome do usuário do SQL Server que pode se conectar ao SQL Server, deixe em branco para usar uma conexão confiável
'/ / StPassword: senha do usuário SQL Servidor

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
' deletar as tabelas local ao sair do sistema
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUserName) = 0 Then
'/ / Use autenticação confiável se stUsername não é fornecido.
stConnect = "ODBC; DRIVER =SQLOLEDB.1;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'/ / AVISO: Isto irá poupar o nome de usuário e a senha com as informações da tabela vinculada.
stConnect = "ODBC;DRIVER=SQLOLEDB.1;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword & ";Option=3;"
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encontrou um erro inesperado: " & Err.Description

End Function

Function CreateConnection()
Dim strPasswd As String
Dim strServer As String
Dim strUser As String
Dim strDB As String
Dim strPort As String

strPasswd = "pilao100%" ' Senha do servidor
strUser = "wesley.soares" ' Usuario do servidor
strServer = ";10.141.151.39" 'IP do servidor
strDB = "Mis" 'Banco de Dado
strPort = "1433" 'Porta do servidor
Rem error
On Error Resume Next 'Tratamento de erro

'Essa tabela devem ser ligados

'A primeira tabela venda é aqui vai ser criada no access a outra dbo. venda é aqui esta no servidor
Set dummy = AttachDSNLessTable("Lancamentos_NOS", "dbo.vwLancamentos_nos", strServer, strDB, strUser, strPasswd)

End Function



Boa Tarde wesleymeim

Código:

Na função AttachDSNLessTable, substitua:


stConnect = "ODBC;DRIVER=SQLOLEDB.1;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword & ";Option=3;"

por:

stConnect = "ODBC;Provider=MSDASQL.1;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & stUserName & ";PWD=" & stPassword & ";DATABASE=" & stDatabase

Não esquece também activar as referencias!

Mas e a string de conexão da porta? Não uso a porta padrão 1433... Teria como referenciar outra porta?

Boa noite!
Pode sim fazer referência a outra porta.