none
Connect SQL SERVER with VBA string from lan(局域网VBA字符串连接访问SQL SERVER) RRS feed

  • 问题

  • Hello

    I Build a sql server developer edition on my computer, and use following connecting string within VBA Macro to connect it, and create table, doing sql request......--

        ConnStr = "Provider=sqloledb; Server = 192.168.50.131, 1433; Database=SettleDatas; User id = sa; Password = Passofsa"

    where 192.168.50.131 is my IP address.

    but when i use same code on some other machine in lan which ip like 192.168.50.***, the prompt is "invalid connection string"(in chinese 无效的链接字符串)

    i've close all the firewall, from property of data link, it's success.

    How can i solve this problem ?

    2010年8月31日 3:44

答案

  • Thanks rmiao and zjcxc.邹建,i've solve this problem.

    "Provider=sqloledb; Server = 192.168.50.131, 1433; Database=SettleDatas; User id = sa; Password = Passofsa

    i delete all the blank char beside the "=" and ";", so everything ok!

     

    • 已标记为答案 SingerCao 2010年8月31日 6:49
    2010年8月31日 6:45

全部回复

  • Ensure you enabled remote access in sql, and can double check sql port by running 'telnet 192.168.50.131 1433' in dos prompt on remote machine. 
    2010年8月31日 4:01
  • Yes, it works.
    2010年8月31日 4:03
  • Telnet works? Does remote machine have sqloledb installed?
    2010年8月31日 4:08
  • microsoft ole db provider for sql server have been installed.
    2010年8月31日 4:16
  • You use that connection string in same VBA Macro?
    2010年8月31日 4:18
  • and, i create a file named test.udl on remote machine, and open lt, fill the parameter, when i select sqloledb driver, it can connect to my machine, when i select sql native client, it failed.

    2010年8月31日 4:20
  • yes. i just copy the same file to remote machine, and change nothing.

    use natstat -na in my conputer, it seem connect port not the 1433 ----

    Active Connections

      Proto  Local Address          Foreign Address        State
      TCP    0.0.0.0:135            0.0.0.0:0              LISTENING
      TCP    0.0.0.0:445            0.0.0.0:0              LISTENING
      TCP    0.0.0.0:1140           0.0.0.0:0              LISTENING
      TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
      TCP    127.0.0.1:1050         0.0.0.0:0              LISTENING
      TCP    127.0.0.1:1434         0.0.0.0:0              LISTENING
      TCP    192.168.50.131:139     0.0.0.0:0              LISTENING
      TCP    192.168.50.131:1092    192.168.50.41:3000     ESTABLISHED
      TCP    192.168.50.131:1093    192.168.50.41:3000     ESTABLISHED
      TCP    192.168.50.131:1094    192.168.50.41:3000     ESTABLISHED
      TCP    192.168.50.131:1096    192.168.50.41:3000     ESTABLISHED
      TCP    192.168.50.131:1097    192.168.50.41:3000     ESTABLISHED

    2010年8月31日 4:27
  • 控制面板, ODBC 数据源里面检查有没有 sql server 对应的 provider, 如果没有的话, 安装 sql server 客户端
    2010年8月31日 4:31
  • 1. sql server    2000.85.....

    2. sql native client     2005.90.....

    which one is the "client of sql server"?

    2010年8月31日 4:48
  • Thanks rmiao and zjcxc.邹建,i've solve this problem.

    "Provider=sqloledb; Server = 192.168.50.131, 1433; Database=SettleDatas; User id = sa; Password = Passofsa

    i delete all the blank char beside the "=" and ";", so everything ok!

     

    • 已标记为答案 SingerCao 2010年8月31日 6:49
    2010年8月31日 6:45