Up sizing study (1 Viewer)

FuzMic

DataBase Tinker
Local time
Today, 22:26
Joined
Sep 13, 2006
Messages
719
Hi members

I had avoided moving away from Access02/03; I was just comfortably enjoying the .mdb even avoided using .accdb from 07. The reason being I feel so good staying in my comfort zone; living with familiar things yet get the job done. At the back of my mind is "small is good" recognizing the eventual point of diminishing return when a system grows huge.

But now with some prompts from members who asked me why I stay on the old place, I down loaded SQL Server 2017 and try my hand on it. My last encounter with SQL Server Express was 2005. At that time I used to be able to up size a mdb to the SQL Server using its database tool. This don’t seem to work any more from A03 up to A2013. What’s happening?

I am now able to import an mdb into a SQL data base going through OLE DB Provider for SQL Server from the SQL Srv 17 Import / Export 32 bit option.

When I look into the ODBC Admin in win10 v1709 I now see 4 drivers.
  1. ODBC Driver 13 for SQL Server
  2. SQL Server
  3. SQL Server Native Client 11.0
  4. SQL Server Native Client RDA 11.0

Which ODBC driver do I use to connect a mdb to the SQL Service?
Can i use OLE DB Provider in Access & how to do it?

Can some of my experts friends enlighten me in my unfamiliar environment i just got in?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:26
Joined
Feb 19, 2002
Messages
42,983
I would use #1. However, keep in mind that you will need to install this driver on ALL user machines also. The default driver is #2 and is installed with Access. It works OK but does not support newer data types. The biggest problem will be dates. You must stick to DateTime and not use any other data type. If I am in control of designing the database, I stick with the old data types so I don't have to deal with distributing drivers to everyone. However, if someone else has designed the BE, they may have used things like BigInt or DateTime(2) and those will not work with driver #2 so I have to distribute driver #1.
 

FuzMic

DataBase Tinker
Local time
Today, 22:26
Joined
Sep 13, 2006
Messages
719
Sir you have always gave the most valuable practical advice, THANK YOU SO MUCH.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:26
Joined
Feb 28, 2001
Messages
27,003
FuzMic, just for the record, Pat is a "ms.", not a "mr."
 

FuzMic

DataBase Tinker
Local time
Today, 22:26
Joined
Sep 13, 2006
Messages
719
Sorry Pat, thousand apologises.
 

Users who are viewing this thread

Top Bottom