Unfortunately, that's not the case based on my experience. You do need an ODBC datasource if you are going to link to the SQL Server tables, which is one of options in the upsizing wizard. If you choose to create a new SQL Server database, it will create and ODBC data source for you, but if you have an existing database, then you have to select an existing ODBC data source or create a new one. You can also create an ADP version of Access, which does connect directly to SQL Server tables, but requires a good deal of work on forms, reports and queries to become functional, and has been deprecated in the lastest version of Access.

The upsizing wizard does a pretty decent job of moving data to SQL Server in my experience, as it preserves data types in most cases (but does create nvarchar text fields in SQL Server which take twice as much space), and preserves default values, indexes and primary keys for the tables, as well as creating constraints that enforce referential integrity.